 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / August 2005Nesting excel greater than 7 arguments
I'm trying to nest greater than 7 arguments, based on a variable rate structure. For example, there are 8 different age catories, and 9 different rates... so depending on the person's age, i need to use a different rate. I have successfully nested the maximum, but it leaves me with three age groups that I can't calculate automatically. Here is my formula so far: =IF(F3>=70,Q3*VolLifeRates!$B$28,IF(F3>=65,Q3*VolLifeRates!$B$27,IF(F3>=60,Q3*VolLifeRates!$B$26,IF(F3>=55,Q3*VolLifeRates!$B$25,IF(F3>=50,Q3*VolLifeRates!$B$24,IF(F3>=45,Q3*VolLifeRates!$B$23,IF(F3>=40,Q3*VolLifeRates!$B$22,IF(F3>=40,Q3*VolLifeRates!$B$21,"manual calc.")))))))) It doesnt work or anyone under 40 :( so i have to manually calculate any clients who's age is less than 40. Grateful if you have a way around Microsoft's maximum! On your sheet VolLifeRates, in cells A17:A28 put 0,20,25,30,35.......70. This assumes that you have rates in cells B17:B28. If not then you should be able to work out what's going on here. The 0 should be against your lowest rate:- Now replace your formula with the following:- =Q3*VLOOKUP(F3,$A$17:$B$28,2) Take a look at the help on the VLOOKUP function  SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- > I'm trying to nest greater than 7 arguments, based on a variable rate > structure. For example, there are 8 different age catories, and 9 [quoted text clipped - 12 lines] > > Grateful if you have a way around Microsoft's maximum! Oops - make that =Q3*VLOOKUP(F3,VolLifeRates!$A$17:$B$28,2) Regards Ken................ Another possible option involving no additional data:- Assuming your first rate starts in B20 =Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,) Regards Ken............... Grrrrrrrr, correction =Q3*OFFSET(VolLifeRates!$B$20,MIN(0,INT(F3/5)-6),) Regards Ken..................... > Another possible option involving no additional data:- > [quoted text clipped - 4 lines] > Regards > Ken............... For crying out loud - Going to bed now!!!!!!!!! =Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),) Regards Ken..................... When I see 5 posts in a row, all from the same author, I figure that either his news reader is broken, and he can't see what he's already posted, OR ... he's got soooo much money in the bank, that he's talking to himself.<g> I can see that your news reader isn't broken.<vbg> SignatureRegards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- For crying out loud - Going to bed now!!!!!!!!! =Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),) Regards Ken..................... LOL - Got fed up with this one - hate making dumb mistakes - grrrrrrrrrrr :-) Regards Ken............... > When I see 5 posts in a row, all from the same author, I figure that > either [quoted text clipped - 9 lines] > Regards > Ken..................... Mayn, you guys are GOOD. I've created another problem now =) I'll post it under a separate heading. THANK YOU!!! > On your sheet VolLifeRates, in cells A17:A28 put 0,20,25,30,35.......70. > This assumes that you have rates in cells B17:B28. If not then you should [quoted text clipped - 23 lines] > > > > Grateful if you have a way around Microsoft's maximum! You have two tests for >=40 here so my solution might need some adjustment =IF(F3<40,"manual calc.",Q3*OFFSET(VolLifeRates!$B$19,VLOOKUP(F3,{0,1;40,2;45,3;50,4;55,5;60,6 ;65,7;70,8},2),0)) SignatureHTH RP (remove nothere from the email address if mailing direct) > I'm trying to nest greater than 7 arguments, based on a variable rate > structure. For example, there are 8 different age catories, and 9 different > rates... so depending on the person's age, i need to use a different rate. I > have successfully nested the maximum, but it leaves me with three age groups > that I can't calculate automatically. > Here is my formula so far: =IF(F3>=70,Q3*VolLifeRates!$B$28,IF(F3>=65,Q3*VolLifeRates!$B$27,IF(F3>=60,Q 3*VolLifeRates!$B$26,IF(F3>=55,Q3*VolLifeRates!$B$25,IF(F3>=50,Q3*VolLifeRat es!$B$24,IF(F3>=45,Q3*VolLifeRates!$B$23,IF(F3>=40,Q3*VolLifeRates!$B$22,IF( F3>=40,Q3*VolLifeRates!$B$21,"manual calc.")))))))) > It doesnt work or anyone under 40 :( so i have to manually calculate any > clients who's age is less than 40. > > Grateful if you have a way around Microsoft's maximum! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.