PPRuNe Forums - View Single Post - Urgent Excel help needed PLEASE!!!!
View Single Post
Old 4th December 2007 | 10:57
  #3 (permalink)  
bnt
15 Anniversary
 
Joined: Feb 2007
Posts: 755
Likes: 26
From: Dublin, Ireland. (No, I just live here.)
Devil

Another possibility is the Lookup Table, using the VLOOKUP or HLOOKUP functions. I'll assume VLOOKUP, since you gave the table in a vertical format.
The way it works is: set up a lookup table on a different part of the spreadsheet, with the upper values from your existing table, that looks something like this. Let's say you put the table in columns D and E, at the top:
0% 5%
10% 10%
21% 15%
31% 20%
41% 25%
So the table is $D$1:$E$5. Then the trick is to use the VLOOKUP formula with the "Approximate" flag set. I don't know exactly what you mean by "uplift", but I'll also assume it's the difference between the two profits: profits in A1 and A2, calculate the uplift in A3, then we do the lookup in A4 as follows:
=VLOOKUP(A3,$D$1:$E$5,2,TRUE)
The 2 means "return the value from column 2 of the table. I put the dollar signs in the table range to lock the references - that means you can copy the formula and the table references won't change. Note that I'm using valid % figures all the way here, so there's no need to multiply or divide by 100. The % figures in the table are translated to the actual numbers automatically e.g. 10% = 0.10. This assumes that the data you put in A1 and A2 are also in % format.
I've just tried this, and it seem to do the trick: when you change A3 from 10% to 11%, A4 changes from 5% to 10%, when A3 says 50%, A4 says 25%. I had to play with the lookup table figures a little, it didn't work correctly first time. Have a look at the VLOOKUP online help too.
bnt is offline  
Reply