New Contributor

Need formula for following

 product tgt sales bonus a bonus b bonus c a 10 20 1.5 1.75 2.5 b 10 20 1.5 1.75 2.5 c 10 20 1.5 1.75 2.5 d 10 20 1.5 1.75 2.5 Need a formula for above to calculate bonus, condition is if c5>=b5 bonus will be c5*1.5 Second condition if c5>=b5 and (c6>=b6 and c7>=b7) then c5*1.75 third condition if c5>=b5 and (c6>=b6, c7>=b7 and c8>=b8) then bonus is b5*2.5

3 Replies

Re: Need formula for following

Here is probably a mistake in reasoning or I do not understand from the previous text what exactly is to be achieved :).
Please specify where is C5, C6, etc..
I can't tell what's where from the text.

My time is too valuable for guessing...for me :).

In addition, if I may recommend, attach a file (without sensitive data) or a photo and use this to describe exactly what you intend to do. Knowing the Excel version, operating system, storage medium could possibly help again.

Thank you for your understanding and patience

NikolinoDE

I know I don't know anything (Socrates)

Re: Need formula for following

Your last bonus outcome seems to be different than Bonus A and B. I'm presuming you meant to say C5*2.5.

This might work:
=CHOOSE(SUM(IF(C5:C8>B5:B8,1,0)),C5*1.5,0,C5*1.75,C5*2.5)

Re: Need formula for following

Thanks a lot Patrick for giving your valuable time. Its resolved.