Simpleish If formula i hope..

[fatreg]

Hi people,

I'm trying to calculate a monetary value based on weekly amout and there maybe an uplift or not.

Essentially, weekly pay is capped at 464 a week, there can be a % uplift in the total, this can then be multiplied to give a number of weeks.

I feel like a right chump but it's stumping me!

</SPAN> 576.92 Weekly 24 Amount of Weeks 10% Uplift 14959.68</SPAN> <TBODY> </TBODY> Total <TBODY> </TBODY>	</SPAN>
</SPAN>
</SPAN>In A4 in the above example I have =IF(A1>464,SUM(464*A3+A1)*A2,SUM(A1*A3+A1)*A2) Yet, on my trusty calculator I make it 464*10%+464 = 510.40 x24 = 12249.60 Why the difference? What am I doing wrong in excel? Please help! my job doesn't quite depend on it, but my sanity does!	</SPAN> <TBODY> </TBODY> </SPAN>

<TBODY>
</TBODY>

 

[bbott]

You're adding 576.92 (A1) in your table example, but in your hand calculated formula, you're adding 464.

=IF(A1>464,SUM(464*A3+A1)*A2,SUM(A1*A3+A1)*A2)

464*10%+464 = 510.40

 

[JRosa]

You're not doing the same math on the calculator as in the formula. Formula says (464*.1+576.92)*24

 

[Snakehips]

Incorrect interpretation - Removed Snakehips

 

[shg]

One possible interpretation of what you said:

	A​	B​
1​	$ 576.92​	Weekly
2​	$ 464.00​	Max
3​	24​	Weeks
4​	10%​	Uplift
5​	$ 12,249.60​	A5: =MIN(A1, A2) * A3 * (1+A4)

 

[fatreg]

thanks chaps/chapesses.

told you I was being dim!

thanks Shg, I like that! future proof too, update one box..

 

[shg]

You're welcome.