Need formula for following

%3CLINGO-SUB%20id%3D%22lingo-sub-3443576%22%20slang%3D%22en-US%22%3ENeed%20formula%20for%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3443576%22%20slang%3D%22en-US%22%3E%3CTABLE%20border%3D%220%22%20width%3D%22512%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3Eproduct%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Etgt%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Esales%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Ebonus%20a%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Ebonus%20b%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Ebonus%20c%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Ea%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3CTD%3E1.75%3C%2FTD%3E%3CTD%3E2.5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Eb%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3CTD%3E1.75%3C%2FTD%3E%3CTD%3E2.5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Ec%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3CTD%3E1.75%3C%2FTD%3E%3CTD%3E2.5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Ed%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3CTD%3E1.75%3C%2FTD%3E%3CTD%3E2.5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENeed%20a%20formula%20for%20above%20to%20calculate%20bonus%2C%20condition%20is%20if%20c5%26gt%3B%3Db5%20bonus%20will%20be%20c5*1.5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ESecond%20condition%20if%20c5%26gt%3B%3Db5%20and%20(c6%26gt%3B%3Db6%20and%20c7%26gt%3B%3Db7)%20then%20c5*1.75%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Ethird%20condition%20if%20c5%26gt%3B%3Db5%20and%20(c6%26gt%3B%3Db6%2C%20c7%26gt%3B%3Db7%20and%20c8%26gt%3B%3Db8)%20then%20bonus%20is%20b5*2.5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%2C%20thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3443576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3455010%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20for%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3455010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1406710%22%20target%3D%22_blank%22%3E%40Umeshmaini%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20probably%20a%20mistake%20in%20reasoning%20or%20I%20do%20not%20understand%20from%20the%20previous%20text%20what%20exactly%20is%20to%20be%20achieved%20%3A).%3CBR%20%2F%3EPlease%20specify%20where%20is%20C5%2C%20C6%2C%20etc..%3CBR%20%2F%3EI%20can't%20tell%20what's%20where%20from%20the%20text.%3C%2FP%3E%3CP%3EMy%20time%20is%20too%20valuable%20for%20guessing...for%20me%20%3A).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%2C%20if%20I%20may%20recommend%2C%20attach%20a%20file%20(without%20sensitive%20data)%20or%20a%20photo%20and%20use%20this%20to%20describe%20exactly%20what%20you%20intend%20to%20do.%20Knowing%20the%20Excel%20version%2C%20operating%20system%2C%20storage%20medium%20could%20possibly%20help%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3455613%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20for%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3455613%22%20slang%3D%22en-US%22%3EYour%20last%20bonus%20outcome%20seems%20to%20be%20different%20than%20Bonus%20A%20and%20B.%20I'm%20presuming%20you%20meant%20to%20say%20C5*2.5.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20might%20work%3A%3CBR%20%2F%3E%3DCHOOSE(SUM(IF(C5%3AC8%26gt%3BB5%3AB8%2C1%2C0))%2CC5*1.5%2C0%2CC5*1.75%2CC5*2.5)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3457978%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20for%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3457978%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot%20Patrick%20for%20giving%20your%20valuable%20time.%20Its%20resolved.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
producttgtsalesbonus abonus bbonus c  
        
a10201.51.752.5  
b10201.51.752.5  
c10201.51.752.5  
d10201.51.752.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

 

Please help, thanks in advance

3 Replies

@Umeshmaini 

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)

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)

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

@Patrick2788