Excel Formula

[KirkComer]

I need a formula to use in a excel spreadsheet...

Cell A1 could be any number between 0 and 12.

I need the formula to reflect this in Cell B1:
If 0 Then 0
If >0 and <= 8.5 Then 8
If >8.5 and <= 9.25 Then 9
If >9.25 Then 11

I'm sure its a simple way to do this without using VBA. I guess my brain is having trouble getting going on a Monday. lol Thanks in advance!

 

[Brianwarnock]

The simplest is nested ifs

If(A1=0,0,if(and(A1>0,A1<=8.5),8,if.....etc

Brian

 

[KirkComer]

Ahhh... thank you Brianwarnock. It worked perfectly. You have just made my day a lot easier.

=IF(A1=0,0,IF(AND(A1>0,A1<=8.5),8,IF(AND(A1>8.5,A1<=9.25),9,IF(A1>9.25,11))))

 

[Mile-O]

Two things that I would say about using nested IFs in this manner is that Excel is limited to eight IFs in one formula and that, with all those brackets going in, it can be a bit much to understand what's going on at a glance when you come back to the formula after some time away.

So, how then to get around the 8-IFs? Well, you can use a VLOOKUP. In the example attached I have created a small table that replicates the original request - lower and upper limits of your bandings and the respective value to return. Then, I've just put in a formula that's much shorter and can manage larger ranges in future.

Worth knowing, should your range need expanded.

 

[Brianwarnock]

Welcome back Mile-o , still a newly registered user of 14 years standing and nearly 11000 posts!
I learnt a lot from you and Pat Hartman in my early days and now i am long retired you are back adding to my posts, yes I would have used VLOOKUP. If the issue had been more complex and should have mentioned it I guess. It is also what Microsoft advise in their tech support

http://office.microsoft.com/en-gb/excel-help/if-HP005209118.aspx

Best wishes

Brian

 

[Mile-O]

Welcome back Mile-o , still a newly registered user of 14 years standing and nearly 11000 posts!

12 years, by my calculation, but yes: still a newbie.

Good to be back. Can't believe it had been about five years.

 

[Mihail]

For this task, the better is (I think) to use an external VBA function.
It is more flexible and can be updated in order to fit to any new, added, request.

 

[Mile-O]

For this task, the better is (I think) to use an external VBA function.

Personally, I think that would be overkill for such a situation. There's no expectation of a flexible requirement, and the range need only be expanded if there are more categories for consideration.

Perhaps if there was a labyrinth of IFs and ELSEs the logic would be better stuck in a function, but why reinvent the wheel when there's no intimation of such complexity?

Another consideration is who the end user of the spreadsheet is. Are their macros going to be switched on, for example? Is the spreadsheet in a trusted location? Are the users even au fait with what this all means?