Hi all:
I have three possibilities: <=100, >100<=200, >200. Instead of using if statements I remember there is a way to set it up as a table going down. The formula goes down the table, finds where the number fits and goes to the right and picks up whatever is there. (Kind of like a lookup but not quite.
I have looked for an example but can't seem to find one. Any help is appreciated.
Max
Last edited by maxthebear; 08-12-2010 at 10:01 PM.
If the ranges were <100, 100 to <200, and 200+, it's easy.
Maybe a few mores words of explanation, max.
Entia non sunt multiplicanda sine necessitate
Basically it is like this:
If your number is 105 so the formula goes down and pulls out 10%
If your number is 210 it would pull out 20%
trying to do it with out nested if statements...
<=100 = 0%
>100<=200 = 10%
>200 = 20%
Something like:
=LOOKUP(A1, {0,101,200}, {0, 0.1, 0.2})
...format the cell with that formula as percentage.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
That works but I need to be able to reference a cell instead of hard numbers since they may change. I should have mentioned that.
Max
If they are indeed whole numbers, then JB's got you covered. Just replace his literal array with a range reference.
If they are not whole numbers, ...
It won't let me put in cell references. Is it because of the brackets??
=lookup(a1, a2:a4, {0,0.1,0.2})
Perfect. Solved. Thanks a bunch.
Max
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks