between calculation?

E

EARTHWALKER

Can anyone help me with a little problem? Im sure you can

I have a row of cells with percentages in so looks something like this


189%
140%
130%
120%
116%

etc etc.

Any way what I want to acheive is in the colum next to this I woul
like a formula that would say if the value in the percentage colum wa
between 200%-175% then it would be 30p. Or if its between 174.99%-150
then it will be 25p. Working its way down to zero.

Can this be done in Excel? As I dont know where to start I have trie
a few formulas myself and got a response of complete mash :
 
N

Norman Harker

Hi Earthwalker!

Use VLOOKUP

In K1:L9 I have:

0% 0
24.99% 0
49.99% 0.05
74.99% 0.1
99.99% 0.15
124.99% 0.2
149.99% 0.25
174.99% 0.3
199.99% 0.3

In B1 I have:

=VLOOKUP(A1,$K$1:$L$9,2)

You could use a formula to achieve a 5p increase at these regular
intervals or internalise the VLOOKUP table, but I think that the above
approach is more flexible for changes you might make to the borders
and amounts.
 
E

EARTHWALKER

Hello norman. I think I followed exactly what you said, but the number
I get in column A are all 0's whatever I change L1 to makes all of
to 0. Perhaps you would be kind enough to explain it a little bette
as I'm bafalled!

Cheers for your reply
 
N

Norman Harker

Hi EarthWalker!

K1:K9 as before. Here is what I have in A1:B7

189% 0.3
140% 0.2
130% 0.2
150% 0.25
175% 0.3
0% 0
0


In B1 I used the formula:

=VLOOKUP(A1,$K$1:$L$9,2)
Copied down to B7

Make sure you have the absolute referencing to the VLOOKUP table
range.

I tested on 0 and a blank cell as well but you might like a varies
response for blank cells pending entry of data.
 
E

EARTHWALKER

ah, I get you now.

so L1:L7 contains the cash, as in 5p to 30p
K1:K7 is the stepped increase percentage.
B1 contains the VLOOKUP
A1 downwards is what you put in to look for an increase to have B
change auto.

I think that is exactly what I wanted. Cheers m8. :
 
N

Norman Harker

Hi EarthWalker!

Good to know you've got it. You'll find VLOOKUP very useful and
flexible in comparison to complex IF functions.
 
Top