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.0 1,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.