Formula Help... [forecasting part values for a period]

Ryan Rooke

The idea of this sheet is to forecast cost allocation. For example, row 3 is allocated 50% until the end of February. What I need the table to show is that 50% of costs are unallocated in Jan/Feb '22 and then from March the value jumps back to 100% as effectively that item become unallocated.

Current formula is:

=IF(DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) >= $[Costed to]7, $[Monthly rate]7 * [%]@row, 0)

Which works perfectly where the % is 100.

When I try to alter this with an IF(OR(... then try to add an expression for it to put 50% of the monthly rate it fails (if % is <1).

Any advice appreciated.

Thanks

Ryan

Genevieve P.

Hi @Ryan Rooke

It sounds like your Costed To column then is being used in two different ways. If the % is 100%, then that date is only relevant once the Month is in the future. However if the % is anything under 100%, then that percentage needs to show in all the months leading up to that Costed To date.

Try the same formula, but instead of looking for 50%, look for anything under 100%:

=IF(AND($[%]@row < 1, DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) <= $[Costed to]@row), $[Example Cost]@row * $[%]@row, IF(DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) >= $[Costed to]@row, $[Example Cost]@row, 0))

Ryan Rooke

Thank you! Spot on. Saves a lot of time now having duplicate entries for part and full allocations.

Thanks again.

Ryan Rooke

Updated current formula

=IF(DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) >= $[Costed to]7, $[Example Cost]7 * [%]@row, 0)

Genevieve P.

Hi @Ryan Rooke

Would you be able to explain what your columns represent a little further?

For example, your formula is currently using the "Costed To" cell as the date to check before entering in any information... so it's using that date as the "Start Date" for the cost to be plugged into the Month column (like in your bottom example with the 100%).

So then for 50%, what is it specifically that you would like it to do? In your screen capture it allocates 50% from that Start date, but in your description it sounds like the "Costed To" for 50% allocation is actually the End Date for when that allocation turns from 50% to 100%, is that correct?

In that instance, we can say, IF the % says 50%, AND if the DATE is greater than or equal to the Costed To date, multiply by the % in this row. Otherwise, IF the DATE is less than or equal to the Costed To date, return the Example Cost (no percentage applied). Otherwise, 0.

So, something like this?

=IF(AND($[%]@row = 0.5, DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) <= $[Costed to]@row), $[Example Cost]@row * $[%]@row, IF(DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) >= $[Costed to]@row, $[Example Cost]@row, 0))

Let me know if I've misunderstood your question!

Cheers,

Genevieve

Ryan Rooke

Hi thanks for the response, I will try to clarify further..

Costed to: this is a date that an item is costed to.% (an end date)

%: is the amount of the Example cost column that is covered (this could be anything, 20% 55% etc)

So using the bottom line in the image, 100% of the €16,084 is costed to 31/5/22. If by the time June comes (and there has been no changes) then the sheet needs to show that the €16,084 is unallocated. Which it does, so the formula is ok for this.

Using line 3 where 50% of €8523 is costed to 28/02/22 is where my current formula falls down. As only 50% is costed to the 28/02/22, months January and February should show €4261.50 (not €0) and then when March/month 3 it should then revert to 100% of the value €8523.

Hopefully that helps?

Genevieve P.

Hi @Ryan Rooke

It sounds like your Costed To column then is being used in two different ways. If the % is 100%, then that date is only relevant once the Month is in the future. However if the % is anything under 100%, then that percentage needs to show in all the months leading up to that Costed To date.

Try the same formula, but instead of looking for 50%, look for anything under 100%:

=IF(AND($[%]@row < 1, DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) <= $[Costed to]@row), $[Example Cost]@row * $[%]@row, IF(DATE([Month 1]$4, [Month 1]$3, [Month 1]$2) >= $[Costed to]@row, $[Example Cost]@row, 0))

Ryan Rooke

Thank you! Spot on. Saves a lot of time now having duplicate entries for part and full allocations.

Thanks again.

Genevieve P.

Wonderful! I'm glad I could help 🙂