MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with formula please


Posted by Charlotte Pulkowski on January 07, 2002 2:31 AM

I need help with the following formula: =IF(O13=0," ",IF(P13="",IF(O13=1,100,IF(O13=2,200,IF(O13=3,300,IF(O13=4,400,IF(O13>=5,500," ")))))*1,IF(O13=1,100,IF(O13=2,200,IF(O13=3,300,IF(O13=4,400,IF(O13>=5,500," "))))))) I need this to also calculate the following: if P13="X" multiply by 0.7 and if P13="XX" multiply by 0.5 and if P13= "XXX" multiply by 0.0 all in the same formula. I do not seem to be able to make it work on all four scenarios.


Posted by Aladin Akyurek on January 07, 2002 4:16 AM

Charlotte --

Not sure about the multiplication part, but it looks like

=IF(O13>0,VLOOKUP(O13,{1,100;2,200;3,300;4,400;5,500},2)*VLOOKUP(LEN(P13),{0,1;1,0.7;2,0.5;3,0},2,0),"")

will do what you want.

Aladin

========

Posted by Charlotte Pulkowski on January 08, 2002 2:39 PM

The solution does not seem to work. The spreadsheet is set up to pay bonuses. O13 = the number of works that they worked and if they worked 5 weeks they would get $500. P13 covers the number of penalties, so if it has two XX's or even the number 2, the bonus would be paid at 50% or $250. The formula is in cell Q13. Do you have any further sugestions? Thanks

Posted by Aladin Akyurek on January 09, 2002 6:48 AM

Charlotte --

I can't visualize the situation with sufficient accuracy. Is it possible that you send me your workbook (scaled down or as is) or post 15 rows data here, along with expected results?

Regards,

Aladin

========

Posted by Charlotte Pulkowski on January 10, 2002 2:00 AM

I really appreciate your time in trying to help me. I am new to this site. I would love to send this little excel sheet to you but am unsure how to do this. Could you please let me know. Thanks