arguments

i am trying to get a formula to increase the dollar amount per $100.00 and
have been able to get the first part. i am not sure how to get the $$$ to
increase since there are a number of arguments per category.

=IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))

this formula will not recalculate over 700.00 and up...any suggestions?
clear explanations would be appreciated since i am new to the formula world.
the last formula i came up withfor a different project was pieced together
from several different spreadsheets.

Because the >=600 condition has already been met............reverse the
order of your conditions, start with 800, then 700 etc.......working your
way down instead of up..........

Vaya con Dios,
Chuck, CABGx3


"gemini0662" <gemini0662@discussions.microsoft.com> wrote in message
news:FCE47366-F825-4BD1-AFC0-06CF9A36CE02@microsoft.com...
> i am trying to get a formula to increase the dollar amount per $100.00 and
> have been able to get the first part. i am not sure how to get the $$$ to
> increase since there are a number of arguments per category.
>
>
=IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20)
)))
>
> this formula will not recalculate over 700.00 and up...any suggestions?
> clear explanations would be appreciated since i am new to the formula
world.
> the last formula i came up withfor a different project was pieced together
> from several different spreadsheets.

The first IF test that evaluates to TRUE causes the associated 'THEN' clause
to be returned. So if the <=600 test fails, we can be sure that the >=600.01
test passes, so the result would be C3+8.
If you have a LOT (>7) different ranges, you'll wind up switching to a
vlookup function rather than nested IFs. Otherwise, arrange your tests
differently:
=if(c3>=800.01,c3+20,if(c3>=700.01,c3+12,if(c3>=600.01,c3+8,c3+6))).


"gemini0662" wrote:

> i am trying to get a formula to increase the dollar amount per $100.00 and
> have been able to get the first part. i am not sure how to get the $$$ to
> increase since there are a number of arguments per category.
>
> =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
>
> this formula will not recalculate over 700.00 and up...any suggestions?
> clear explanations would be appreciated since i am new to the formula world.
> the last formula i came up withfor a different project was pieced together
> from several different spreadsheets.

If you want to use if statements, you have to reorganize it. Once a true
statement is found, the rest of the formula is not processed. Anything above
600.01 will return 8 because this part IF(C3>=600.01,C3+8 will always be true.

=C3+IF(C3>=800.01,20,IF(C3>=700.01,12,IF(C3>=600.01,8,6)))

However, since Excel has a limit of 7 nested functions, I generally avoid
using IF statements like this. You could try the following, which uses a
lookup (check excel help for VLOOKUP and the link below - post back if you
still have questions)
http://www.contextures.com/xlFunctions02.html

=C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20},2,1)

Instead of putting the lookup table directly in the formula you could create
the table somewhere in your workbook. and just reference it in the VLOOKUP
formula. So if the lookup table were in cells A1:B4

A B
0 6
600.01 8
700.01 12
800.01 20

the formula becomes

=C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples
in excel help.







"gemini0662" wrote:

> i am trying to get a formula to increase the dollar amount per $100.00 and
> have been able to get the first part. i am not sure how to get the $$$ to
> increase since there are a number of arguments per category.
>
> =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
>
> this formula will not recalculate over 700.00 and up...any suggestions?
> clear explanations would be appreciated since i am new to the formula world.
> the last formula i came up withfor a different project was pieced together
> from several different spreadsheets.

thanks, this helped a lot...now...how do i get it to hide the #NA if the
value is 0?

marcia

"JMB" wrote:

> If you want to use if statements, you have to reorganize it. Once a true
> statement is found, the rest of the formula is not processed. Anything above
> 600.01 will return 8 because this part IF(C3>=600.01,C3+8 will always be true.
>
> =C3+IF(C3>=800.01,20,IF(C3>=700.01,12,IF(C3>=600.01,8,6)))
>
> However, since Excel has a limit of 7 nested functions, I generally avoid
> using IF statements like this. You could try the following, which uses a
> lookup (check excel help for VLOOKUP and the link below - post back if you
> still have questions)
> http://www.contextures.com/xlFunctions02.html
>
> =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20},2,1)
>
> Instead of putting the lookup table directly in the formula you could create
> the table somewhere in your workbook. and just reference it in the VLOOKUP
> formula. So if the lookup table were in cells A1:B4
>
> A B
> 0 6
> 600.01 8
> 700.01 12
> 800.01 20
>
> the formula becomes
>
> =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples
> in excel help.
>
>
>
>
>
>
>
> "gemini0662" wrote:
>
> > i am trying to get a formula to increase the dollar amount per $100.00 and
> > have been able to get the first part. i am not sure how to get the $$$ to
> > increase since there are a number of arguments per category.
> >
> > =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
> >
> > this formula will not recalculate over 700.00 and up...any suggestions?
> > clear explanations would be appreciated since i am new to the formula world.
> > the last formula i came up withfor a different project was pieced together
> > from several different spreadsheets.

If the value is zero the formulae return 6 on my machine, same as your
original formula.

Can the value in C3 be negative?

You can test for #n/a with
=IF(ISNA(VLOOKUP(.......), "", VLOOKUP(........))

but the formula will be longer than the nested IF's. If you don't know what
the lower end of the range can be for C3 and there are only 4 conditions,
consider using the nested IF's. I brought up VLOOKUP because it is usually a
good solution when you start adding more levels.

"gemini0662" wrote:

> thanks, this helped a lot...now...how do i get it to hide the #NA if the
> value is 0?
>
> marcia
>
> "JMB" wrote:
>
> > If you want to use if statements, you have to reorganize it. Once a true
> > statement is found, the rest of the formula is not processed. Anything above
> > 600.01 will return 8 because this part IF(C3>=600.01,C3+8 will always be true.
> >
> > =C3+IF(C3>=800.01,20,IF(C3>=700.01,12,IF(C3>=600.01,8,6)))
> >
> > However, since Excel has a limit of 7 nested functions, I generally avoid
> > using IF statements like this. You could try the following, which uses a
> > lookup (check excel help for VLOOKUP and the link below - post back if you
> > still have questions)
> > http://www.contextures.com/xlFunctions02.html
> >
> > =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20},2,1)
> >
> > Instead of putting the lookup table directly in the formula you could create
> > the table somewhere in your workbook. and just reference it in the VLOOKUP
> > formula. So if the lookup table were in cells A1:B4
> >
> > A B
> > 0 6
> > 600.01 8
> > 700.01 12
> > 800.01 20
> >
> > the formula becomes
> >
> > =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples
> > in excel help.
> >
> >
> >
> >
> >
> >
> >
> > "gemini0662" wrote:
> >
> > > i am trying to get a formula to increase the dollar amount per $100.00 and
> > > have been able to get the first part. i am not sure how to get the $$$ to
> > > increase since there are a number of arguments per category.
> > >
> > > =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
> > >
> > > this formula will not recalculate over 700.00 and up...any suggestions?
> > > clear explanations would be appreciated since i am new to the formula world.
> > > the last formula i came up withfor a different project was pieced together
> > > from several different spreadsheets.

well, i did as you suggested using the vlook up you listed. i put the values
on a seperate page in the work book in case the dollar value would ever
change. at one point and time i was using 500 lines for one formula. i just
gave the worksheet a new line with 0 as a default and it works well enough.
the value can never be negative i don't think since it is a fee schedule. i
am not sure i would know how to run the formula to test it. i will try and
test it some other time when i have nothing else to do. thanks for all your
help. i learned a lot.


"JMB" wrote:

> If the value is zero the formulae return 6 on my machine, same as your
> original formula.
>
> Can the value in C3 be negative?
>
> You can test for #n/a with
> =IF(ISNA(VLOOKUP(.......), "", VLOOKUP(........))
>
> but the formula will be longer than the nested IF's. If you don't know what
> the lower end of the range can be for C3 and there are only 4 conditions,
> consider using the nested IF's. I brought up VLOOKUP because it is usually a
> good solution when you start adding more levels.
>
> "gemini0662" wrote:
>
> > thanks, this helped a lot...now...how do i get it to hide the #NA if the
> > value is 0?
> >
> > marcia
> >
> > "JMB" wrote:
> >
> > > If you want to use if statements, you have to reorganize it. Once a true
> > > statement is found, the rest of the formula is not processed. Anything above
> > > 600.01 will return 8 because this part IF(C3>=600.01,C3+8 will always be true.
> > >
> > > =C3+IF(C3>=800.01,20,IF(C3>=700.01,12,IF(C3>=600.01,8,6)))
> > >
> > > However, since Excel has a limit of 7 nested functions, I generally avoid
> > > using IF statements like this. You could try the following, which uses a
> > > lookup (check excel help for VLOOKUP and the link below - post back if you
> > > still have questions)
> > > http://www.contextures.com/xlFunctions02.html
> > >
> > > =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20},2,1)
> > >
> > > Instead of putting the lookup table directly in the formula you could create
> > > the table somewhere in your workbook. and just reference it in the VLOOKUP
> > > formula. So if the lookup table were in cells A1:B4
> > >
> > > A B
> > > 0 6
> > > 600.01 8
> > > 700.01 12
> > > 800.01 20
> > >
> > > the formula becomes
> > >
> > > =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples
> > > in excel help.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "gemini0662" wrote:
> > >
> > > > i am trying to get a formula to increase the dollar amount per $100.00 and
> > > > have been able to get the first part. i am not sure how to get the $$$ to
> > > > increase since there are a number of arguments per category.
> > > >
> > > > =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
> > > >
> > > > this formula will not recalculate over 700.00 and up...any suggestions?
> > > > clear explanations would be appreciated since i am new to the formula world.
> > > > the last formula i came up withfor a different project was pieced together
> > > > from several different spreadsheets.

The 0 is to capture anything below 600.01. The VLOOKUP is looking for the
biggest number that is less than/equal to C3. So without the zero you'll get
the #N/A. You can change the table to return a zero if C3 = 0 and 6 for
anything between 0.01 and 600 (inclusive).

A B
0 0
0.01 6
600.01 8
700.01 12
800.01 20

As you noticed one benefit is it is easier to change the schedule
(particularly if you have more than just a few cells referencing the table).

"gemini0662" wrote:

> well, i did as you suggested using the vlook up you listed. i put the values
> on a seperate page in the work book in case the dollar value would ever
> change. at one point and time i was using 500 lines for one formula. i just
> gave the worksheet a new line with 0 as a default and it works well enough.
> the value can never be negative i don't think since it is a fee schedule. i
> am not sure i would know how to run the formula to test it. i will try and
> test it some other time when i have nothing else to do. thanks for all your
> help. i learned a lot.
>
>
> "JMB" wrote:
>
> > If the value is zero the formulae return 6 on my machine, same as your
> > original formula.
> >
> > Can the value in C3 be negative?
> >
> > You can test for #n/a with
> > =IF(ISNA(VLOOKUP(.......), "", VLOOKUP(........))
> >
> > but the formula will be longer than the nested IF's. If you don't know what
> > the lower end of the range can be for C3 and there are only 4 conditions,
> > consider using the nested IF's. I brought up VLOOKUP because it is usually a
> > good solution when you start adding more levels.
> >
> > "gemini0662" wrote:
> >
> > > thanks, this helped a lot...now...how do i get it to hide the #NA if the
> > > value is 0?
> > >
> > > marcia
> > >
> > > "JMB" wrote:
> > >
> > > > If you want to use if statements, you have to reorganize it. Once a true
> > > > statement is found, the rest of the formula is not processed. Anything above
> > > > 600.01 will return 8 because this part IF(C3>=600.01,C3+8 will always be true.
> > > >
> > > > =C3+IF(C3>=800.01,20,IF(C3>=700.01,12,IF(C3>=600.01,8,6)))
> > > >
> > > > However, since Excel has a limit of 7 nested functions, I generally avoid
> > > > using IF statements like this. You could try the following, which uses a
> > > > lookup (check excel help for VLOOKUP and the link below - post back if you
> > > > still have questions)
> > > > http://www.contextures.com/xlFunctions02.html
> > > >
> > > > =C3+VLOOKUP(C3,{0,6;600.01,8;700.01,12;800.01,20},2,1)
> > > >
> > > > Instead of putting the lookup table directly in the formula you could create
> > > > the table somewhere in your workbook. and just reference it in the VLOOKUP
> > > > formula. So if the lookup table were in cells A1:B4
> > > >
> > > > A B
> > > > 0 6
> > > > 600.01 8
> > > > 700.01 12
> > > > 800.01 20
> > > >
> > > > the formula becomes
> > > >
> > > > =C3+VLOOKUP(C3,A1:B4,2,1) which will probably look more like the examples
> > > > in excel help.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "gemini0662" wrote:
> > > >
> > > > > i am trying to get a formula to increase the dollar amount per $100.00 and
> > > > > have been able to get the first part. i am not sure how to get the $$$ to
> > > > > increase since there are a number of arguments per category.
> > > > >
> > > > > =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
> > > > >
> > > > > this formula will not recalculate over 700.00 and up...any suggestions?
> > > > > clear explanations would be appreciated since i am new to the formula world.
> > > > > the last formula i came up withfor a different project was pieced together
> > > > > from several different spreadsheets.

The syntax of your formula is OK, it's the logic that's lacking.

Let's take your second argument,
IF(C3>=600.01

NOW, doesn't that equate to "True" when C3 contains a value of say, 1000?

That being the case, the formula calculates,
C3+8
AND then *stops* calculating, since a true condition has been met.
It (the formula) never even reaches the other arguments to evaluate them.

What you have to do to 'fix' your formula is reverse the arguments.

Put the larger values at the front of the formula so that the evaluation
does not skip over the lesser values.

Also, take *all* possibilities into consideration, if necessary!

Is it important that if C3 is 0 or negative (<0), or even empty, it will
return the first calculation of
C3+6
?
Is that what you really want to happen (maybe you do)?
If C3 has not yet even been keyed in a value, your formula cell will still
display a 6.

Does this help?
--
HTH,

RD

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

"gemini0662" <gemini0662@discussions.microsoft.com> wrote in message
news:FCE47366-F825-4BD1-AFC0-06CF9A36CE02@microsoft.com...
>i am trying to get a formula to increase the dollar amount per $100.00 and
> have been able to get the first part. i am not sure how to get the $$$ to
> increase since there are a number of arguments per category.
>
> =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
>
> this formula will not recalculate over 700.00 and up...any suggestions?
> clear explanations would be appreciated since i am new to the formula
> world.
> the last formula i came up withfor a different project was pieced together
> from several different spreadsheets.

Excuse my clock!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:uR9b$QVpGHA.4812@TK2MSFTNGP04.phx.gbl...
> The syntax of your formula is OK, it's the logic that's lacking.
>
> Let's take your second argument,
> IF(C3>=600.01
>
> NOW, doesn't that equate to "True" when C3 contains a value of say, 1000?
>
> That being the case, the formula calculates,
> C3+8
> AND then *stops* calculating, since a true condition has been met.
> It (the formula) never even reaches the other arguments to evaluate them.
>
> What you have to do to 'fix' your formula is reverse the arguments.
>
> Put the larger values at the front of the formula so that the evaluation
> does not skip over the lesser values.
>
> Also, take *all* possibilities into consideration, if necessary!
>
> Is it important that if C3 is 0 or negative (<0), or even empty, it will
> return the first calculation of
> C3+6
> ?
> Is that what you really want to happen (maybe you do)?
> If C3 has not yet even been keyed in a value, your formula cell will still
> display a 6.
>
> Does this help?
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "gemini0662" <gemini0662@discussions.microsoft.com> wrote in message
> news:FCE47366-F825-4BD1-AFC0-06CF9A36CE02@microsoft.com...
>>i am trying to get a formula to increase the dollar amount per $100.00 and
>> have been able to get the first part. i am not sure how to get the $$$
>> to
>> increase since there are a number of arguments per category.
>>
>> =IF(C3<=600,C3+6,IF(C3>=600.01,C3+8,IF(C3>=700.01,C3=12,IF(C3>=800.01,C3+20))))
>>
>> this formula will not recalculate over 700.00 and up...any suggestions?
>> clear explanations would be appreciated since i am new to the formula
>> world.
>> the last formula i came up withfor a different project was pieced
>> together
>> from several different spreadsheets.
>