If formula

[workingmom434]

trying to get a formula to calculate a percentage of salary if you hit different percentages of your budget. If B32@ is less than 96%, you get 0% of your salary ($g$13),

if 96% you get 3.0%,
if 97% you get 3.5%,
if 98%, you get 4%
if 99%, you get 4.5%
if 100%, you get 5%

I think I am close but missing something. Any help is greatly appreciated.


(IF(B32<0.96,0,IF(B32<.97,0.3,(b32<.98,0.35(ifb32<.99,.04,,0.05))/$G$13

[etaf]

if 96% you get 3.0%,
if 97% you get 3.5%,
if 98%, you get 4%
if 99%, you get 4.5%
if 100%, you get 5%

=IF( B32 < 0.96 , $G$13*0 , IF ( B32 < 0.97 , $G$13* 0.03 , IF ( B32 < 0.98 , $G$13* 0.035 ,IF ( B32 < 0.99 , $G$13* 0.04 , IF ( B32 < 1 , $G$13* 0.045 , $G$13* 0.05 )))))

[Snuggles]

May I suggest using a table and a VLookup to get your answer?
In that way, if your percentages change in the future, you need not change each formula, just the table.
My two cents worth . . . with an example:

Budget 97.4%
Raise 3.5%
=IFERROR(VLOOKUP(B1,A5:B9,2,1),0)

96% 3.0%
97% 3.5%
98% 4.0%
99% 4.5%
100% 5.0%

Have a great day !! -- Snuggles

[jason.b75]

Try

=LOOKUP(N(B32),{0,0.96,0.97,0.98,0.99,1},{0,0.03,0.035,0.04,0.045,0.05})/$G$13

Please have a read of the forum rules regarding thread titles, in my opinion, your title falls between 'anticipated solution' and 'poor thread title'.

If you look at the formula above, and the suggestion from Snuggles in post #3, there is no use of the IF function in either.

I would suggest that you change your title to something more meaningful, however, I'm not making it a formal moderation request.

[workingmom434]

Thank you for all your feedback and help. I have never used a v lookup but will see if I can figure it out as maybe that will help with my other post.

[TMS]

@jason:

however, I'm not making it a formal moderation request.

As a senior member (you, that is), I suspect it could be considered a formal (moderation) request and, strictly speaking, it needs to be complied with. Should be anyway