I'm trying to create this attached bonus calculation sheet and I'm baffled on how to create a cleaner more simple formula that a huge nested IF function. I don't know where to go from here. I need to be able to enter a percentage into the yellow box and have the red box return the bonus amount based on whichever range the percentage falls into.

On Monday, March 11, 2013 7:08:22 PM UTC-7, Josh M. wrote:
I'm trying to create this attached bonus calculation sheet and I'm

baffled on how to create a cleaner more simple formula that a huge

nested IF function. I don't know where to go from here. I need to be

able to enter a percentage into the yellow box and have the red box

return the bonus amount based on whichever range the percentage falls

into.





+-------------------------------------------------------------------+

|Filename: Excel Pic.jpg |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=806|

+-------------------------------------------------------------------+







--

Josh M.

In C2 try =VLOOKUP(C1,B5:C15,2)

Regards,
Howard

Hi Josh M

Use the VLOOKUP with how the data you already have in place.

In cell C2: =VLOOKUP(C1,A5:C15,3,1)

You do not need the data in B5:B15

Have a look at this link for VLOOKUP.

http://www.myonlinetraininghub.com/?s=VLOOKUP

Kevin

Thanks everyone. Problem solved.

Josh,

Out of interest and seeing as this seems to be a duplicate/secondary post from the post I provided you with a solution using the 'Nasty' stack of If statements.

1. Did the previous post I took the time to answer give you what you wanted?

2. In the first and this subsequent post your percentage ranges seem to be hard coded and if I inturpret correctly will not match in all cases.

For Example if its 1% to 100% bring back $X amount, if its 0% to 0.9% bring back $Y amount.

Q. What happens if you enter between 0.9% and 1%?

Regards,

Gizzmo