Excel Formula

[pigeonguyuk]

Hi all anyone able to do me a formula for the following

what i want is the following

if cell A1 is 0-14 then (output cell) = £0
if cell A1 is 15-19 then (output cell) = £125
if cell A1 is 19-24 then (output cell) = £200
if cell A1 is 25+ then (output cell) = £125


would need all combined within the one formula if possible?

 

[pigeonguyuk]

all sorted got the formula below to work nicely. Just needed to reverse the order from highest to lowest


=IF(O13>=25,250,IF(O13>=20,200,IF(O13>=15,125,IF(O13>0,0,0))))

 

[monkeypants]

You could do:

=IF(AND(A1>=0,A1<=14),0,IF(AND(A1>=15,A1<=19),125,IF(AND(A1>=19,A1<=24),200,IF(A1>=25,250,0))))

Something about your formula doesn't quite sit right with me but I can't put my finger on what.

 

[FoxEye]

Something about your formula doesn't quite sit right with me but I can't put my finger on what.

Apart from his last IF being totally redundant, it looks OK to me.

=IF(NOT(ISNUMBER(O13)),NA(),IF(O13>=25,250,IF(O13>=20,200,IF(O13>=15,125,0))))

That any better?

 

[james.miller]

You could do:

=IF(AND(A1>=0,A1<=14),0,IF(AND(A1>=15,A1<=19),125,IF(AND(A1>=19,A1<=24),200,IF(A1>=25,250,0))))

Something about your formula doesn't quite sit right with me but I can't put my finger on what.

<=19 and >= 19, you have 19 there twice.


formulas like that are messy, I'd usally write them as a function.

Function myVal(vIn As Double) As Double
    Select Case vIn
        Case 0 To 14
            myVal = 0
        Case 15 To 18
            myVal = 125
        Case 19 To 24
            myVal = 200
        Case is > 24
            myVal = 125
    End Select
End Function

put that in a vba module (Alt +f11, insert module)

then in the cell on the sheet just type in

=myVal(a1)

Much neater, easier to modify without your brain imploding, too

 

[wesimmo]

Or use a VLOOKUP with TRUE and have a little table with your limits and what you want to return.

Is the most elegant formula only solution in my opinion and is really easy to change the various values. It works well with more bandings too when the formulas get really horrible to read due to all the nesting.

edit: just to explain more clearly.

If you use TRUE or 1 in the vlookup it doesn't look for an exact match it checks the lookup table for the next largest number.

So you'd set you table up as

0 - 0
15 - 125
19 - 200
25 - 125

 

[cats_five]

Think the VLOOKUP with TRUE will return 0 if the cell contains less than 0.

 

[wesimmo]

It will error if it's less than 0 as it won't find next highest match in that particular table i did as the lowest number was 0.

Otherwise negative numbers work just the same.

 

[413x]

I hate vlookup always use index match

By the sound of it the if it's the best for the op

 

[wesimmo]

I hate vlookup always use index match

By the sound of it the if it's the best for the op

Not sure why you'd want to use more functions to do what one can do when it's the right place to use a lookup?

In this instance the vlookup is much more efficient, you don't have to edit every cell when you want to change the bandings, it's infinitely more flexible when you start using more bandings and it's easier to read for someone else looking at the spreadsheet.