LinkBack URL
About LinkBacks
If age is between 30 -39, put $162.40; if age is between 50 - 54, put $293.60,etc,etc
Thank you, gurus!
If age is between 30 -39, put $162.40; if age is between 50 - 54, put $293.60,etc,etc
Thank you, gurus!
The worksheet function would look like this;
In my opinion, many people get carried away with long worksheet functions. They get difficult to read and manage.
Please Login or Register to view this content.
Any larger than this one and I would make a custom worksheet function that looks like =Age2Value(A1).
Last edited by Dennis7849; 03-27-2012 at 03:32 PM.
You can use something like this.
=IF(AND(A1>" ",A3<40),"$162.40",IF(AND(A1>" ",A3<55),"293.60",IF(AND(A1>" ",A3<60),"$400.00"," ")))
This assumes you have data to enter in cell A1, for example, the person's name. And in cell A3 you enter their age. Of course A1 can be filled with anything really. the formula only requires something other than a blank be there. And this formula would go in a cell other than A1 or A3. Hope that helps.
Alfred-
It is not necessary to use an "AND" statement if you start with the smallest number and work your way up or start with the largest and work your way down as follows:
=if(A1<30,150,if(A1<40,162.40,if(A1<55,293.60, if(A1<...
You can also skip the nested IF's and just use a LOOKUP in ascending order, e.g.
=LOOKUP(A1,{30,40,50,60},{162.40,192.80,223.20,253.60})
The first array needs to be in ascending order, the second simply correlates to that (could be in descending if your $ amounts went down with age, for example).
This works beautifully. Thank you very much!
Thank you very much. As Dyonisos said, the AND was not necessary though.
I am going to be cheeky and ask a continuation of this if statemment with one more condition: andif M2 has a number in it (any number), then.... How do I connect this to the previous series of if statements? Thanks very much for the help.
So if M2 doesn't have a number, don't do the calculation at all? In that case..
=IF(ISNUMBER(M2),LOOKUP(A1,{1,2,3,4},{3.3,4.4,2.2,6.6}),"")
=if(ISNUMBER(m2)=true,[nested if statements],"")
No need for the "=true" bit, as ISNUMBER() will return TRUE or FALSE and that will be used to determine which argument in the IF statement is acted upon.
Thanks Paul!
You, Guys are too smart, but I am not. I tried to connect the isnumber and if, but I did something wrong because it didn't work.
=IF(ISNUMBER(S2),(E2<30,137.5,IF(E2<=39,162.4,IF(E2<=49,220,IF(E2<=54,293.6,IF(E2<=59,364.8,IF(E2<=64,468,IF(E2>=65,567.2))))))),"")
Probably don't know how to place my parenthesis.
Any reason why you'd want to continue using nested IF statements when the LOOKUP function is cleaner? Even if you did stick with that, you need to included the IF after the ISNUMBER argument.
=IF(ISNUMBER(S2),LOOKUP(E2,{0,30,40,50,55,60,65},{137.5,162.4,220,293.6,364.8,468,567.2}),"")
Because what could I say? That I like suffering? :D Thank you very much! It works great.
Glad it works.
If you're satisfied that your problem is resolved, please do mark the thread as Solved. To do so, in your first post in the thread click 'Thread Tools' (just above the post) and then 'Mark this thread as solved'.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks