arguments

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.