Nesting excel greater than 7 arguments

Mayn, you guys are GOOD. I've created another problem now =) I'll post it
under a separate heading. THANK YOU!!!

"Ken Wright" wrote:

> 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
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------*------------------------------*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------*------------------------------*----------------
>
>
> "Chris Berding" <Chris [email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

Grrrrrrrr, correction

=Q3*OFFSET(VolLifeRates!$B$20,MIN(0,INT(F3/5)-6),)

Regards
Ken.....................


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Another possible option involving no additional data:-
>
> Assuming your first rate starts in B20
>
> =Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,)
>
> 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...............

For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

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))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

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>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
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...............


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> For crying out loud - Going to bed now!!!!!!!!!
>
> =Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)
>
> Regards
> Ken.....................
>
>
>

Oops - make that

=Q3*VLOOKUP(F3,VolLifeRates!$A$17:$B$28,2)

Regards
Ken................

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

Oops - make that

=Q3*VLOOKUP(F3,VolLifeRates!$A$17:$B$28,2)

Regards
Ken................

LOL - Got fed up with this one - hate making dumb mistakes - grrrrrrrrrrr
:-)

Regards
Ken...............


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> For crying out loud - Going to bed now!!!!!!!!!
>
> =Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)
>
> Regards
> Ken.....................
>
>
>

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))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

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>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

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...............

Mayn, you guys are GOOD. I've created another problem now =) I'll post it
under a separate heading. THANK YOU!!!

"Ken Wright" wrote:

> 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
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "Chris Berding" <Chris [email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

Grrrrrrrr, correction

=Q3*OFFSET(VolLifeRates!$B$20,MIN(0,INT(F3/5)-6),)

Regards
Ken.....................


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Another possible option involving no additional data:-
>
> Assuming your first rate starts in B20
>
> =Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,)
>
> Regards
> Ken...............
>
>

LOL - Got fed up with this one - hate making dumb mistakes - grrrrrrrrrrr
:-)

Regards
Ken...............


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> 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>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

Mayn, you guys are GOOD. I've created another problem now =) I'll post it
under a separate heading. THANK YOU!!!

"Ken Wright" wrote:

> 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
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "Chris Berding" <Chris [email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

Grrrrrrrr, correction

=Q3*OFFSET(VolLifeRates!$B$20,MIN(0,INT(F3/5)-6),)

Regards
Ken.....................


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Another possible option involving no additional data:-
>
> Assuming your first rate starts in B20
>
> =Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,)
>
> 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...............

Oops - make that

=Q3*VLOOKUP(F3,VolLifeRates!$A$17:$B$28,2)

Regards
Ken................

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))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

Grrrrrrrr, correction

=Q3*OFFSET(VolLifeRates!$B$20,MIN(0,INT(F3/5)-6),)

Regards
Ken.....................


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Another possible option involving no additional data:-
>
> Assuming your first rate starts in B20
>
> =Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,)
>
> Regards
> Ken...............
>
>

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

Mayn, you guys are GOOD. I've created another problem now =) I'll post it
under a separate heading. THANK YOU!!!

"Ken Wright" wrote:

> 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
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "Chris Berding" <Chris [email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

Oops - make that

=Q3*VLOOKUP(F3,VolLifeRates!$A$17:$B$28,2)

Regards
Ken................

LOL - Got fed up with this one - hate making dumb mistakes - grrrrrrrrrrr
:-)

Regards
Ken...............


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> 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>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

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))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

Another possible option involving no additional data:-

Assuming your first rate starts in B20

=Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,)

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>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

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))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

LOL - Got fed up with this one - hate making dumb mistakes - grrrrrrrrrrr
:-)

Regards
Ken...............


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> For crying out loud - Going to bed now!!!!!!!!!
>
> =Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)
>
> 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...............

Oops - make that

=Q3*VLOOKUP(F3,VolLifeRates!$A$17:$B$28,2)

Regards
Ken................

Mayn, you guys are GOOD. I've created another problem now =) I'll post it
under a separate heading. THANK YOU!!!

"Ken Wright" wrote:

> 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
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "Chris Berding" <Chris [email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

For crying out loud - Going to bed now!!!!!!!!!

=Q3*OFFSET(VolLifeRates!$B$20,MAX(0,INT(F3/5)-6),)

Regards
Ken.....................

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Chris Berding" <Chris [email protected]> wrote in message
news:[email protected]...
> 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!

Grrrrrrrr, correction

=Q3*OFFSET(VolLifeRates!$B$20,MIN(0,INT(F3/5)-6),)

Regards
Ken.....................


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Another possible option involving no additional data:-
>
> Assuming your first rate starts in B20
>
> =Q3*OFFSET(VolLifeRates!$B$20,F3/5-6,)
>
> Regards
> Ken...............
>
>

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!