Conditional multplication in excel

I am building a forecast sheet for a new project and I am stuck on how to conditional multiplication in excel.

what I am trying to achieve is:

A1 = 10 | A2 = 10 | A3 = 5

A1 * A2 (until a total of 1000) then change to A1 * A3

It needs to use A2 up to the 1000 mark then from 1000 on use A3 so I can use the standard if If >1000 then use A3 formula.

this is an electricity forecast as I have a set rate (A2) till '1000' units is consumed then I am given rate (A3) and I wanted to know if this can be automated so I can create different models without needing to manually rework it every time?

Thanks and I hope that makes sense :)

1 answer

  • answered 2017-11-23 08:06 Chris

    This is difficult to calculate in a single column format. The below image is how I would approach this problem.

    Range D10:E21 contains the formula =MIN(D$8-SUM(D$9:D9),$B10-SUM($C10:C10)) Range D23:E23 contains the formula =D7*SUM(D10:D22)

    Hope this helps.

    enter image description here