Thread: Calculations don't add up

I am having quite a time with this sheet. The formula, unless I can't see the obvious, looks like it should work perfectly. However, the outcome is more than it should be.

eg: one sheet of red costs 461.00
one sheet of blue costs 595.00
different jobs require a different number of sheets

=IF(G7>0,(1000-L2+P3))+IF(G7>30,(900-(L2*2)+(P3*2)))+IF(G7>60,(1000-(l2*3)+(P3*3)))+IF(G7>90,(1000-(L2*4)+(P3*4)))+IF(G7>120,(1000-(L2*5)+(P3*5)))+IF(G7>150,(1000-(L2*6)+(P3*6)))+IF(G7>180,(1000-(L2*7)+(P3*7)))+IF(G7>210,(1000-(L2*8)+(P3*8)))+IF(G7>240,(1000-(L2*9)+(P3*9)))+IF(G7>270,(1000-(L2*10)+(P3*10)))+IF(G7>300,"Please Call")

if g7 shows that the job requires one sheet of blue...
1000 (material & labor) - 461 (cost of red) + 595 (cost of blue) = 1134
calculator & excel agree

if g7 shows that the job requires two sheets of blue...
1000+900=1900 (material & labor) - [461 (cost of red) *2] + [595 (cost of blue) *2] = ?
calculator 2168
excel 2302

this problem gets worse as I go up in sheets.

Please help!

Looks to me like an error entering the formula.

2168 does indeed equal 1900-2*461+2*595
2302 equals 1900-3*461+3*595

I haven't been through your formula in detail, but it looks like when you want it to use 2 sheets, it is erroneously calcualating as if there are 3 sheets.

I see it now... it is calculating three sheets. It is calculating >0 and >30 before it stops.
mmmmmm, now to figure out how to keep them seperate.

Does this work?

=IF(G7>300,"Please Call",IF(AND(G7>0,G7<=30),(1000-L2+P3),900-(L2*INT((G7-1)/30))+(P3*INT((G7-1)/30))))

This solves the problem. Thank you for your insite... it allowed me to step back and relax.

=IF(G7>0,(1000-L2+P3))+IF(G7>30,(900-L2+P3))+IF(G7>60,(1000-L2+P3))+IF(G7>90,(1000-L2+P3))+IF(G7>120,(1000-L2+P3))+IF(G7>150,(1000-L2+P3))+IF(G7>180,(1000-L2+P3))+IF(G7>210,(1000-L2+P3))+IF(G7>240,(1000-L2+P3))+IF(G7>270,(1000-L2+P3))+IF(G7>300,"Please Call")

NVBC,
I did try it and it did not.

Maybe it should be:

=IF(G7>300,"Please Call",IF(AND(G7>0,G7<=30),(1000-L2+P3),900-(L2*(INT((G7-1)/30)+1))+(P3*(INT((G7-1)/30)+1))))