IF function syntax question - please help. Thanks!

[EV]

Hi, I appreciate any help with this problem. Thanks so much in advance!
I need to create a formula that looks at several cells, and
performs several functions and every time I try I get an invalid or
other error, so I'm having trouble with the syntax.

I need the formula to say:

If fee (N7) equal "YES" then,
In 'Total Fees' cell (O7)
If price (G7) equals $0 - $25, then multiply by 5.25% (other
worksheet!I27)
or
If price (G7) equals $25 - $1,000 then multiply by 5.25% (other
worksheet!I27) of the initial $25 ($1.31), plus 2.75% (other
worksheet!I28) of the remainder (up to 1000)
or
If price (G7) equals over $1,000 then multiply by 5.25% (other
worksheet!I27)of the initial $25 ($1.31), plus 2.75% (other
worksheet!I28) of the initial $25 - $1000 ($26.81), plus 1.50% (other
worksheet!I29) of the remainder ($1000.01 and up)
else,
If fee (N7) equal "No" then, "0"

Thanks for any help !!!
Regards,
E

 

[Jerry W. Lewis]

What you are trying to do is not clear. Repeating the question verbatim
does not make it any clearer.

Help is pretty clear for the worksheet IF() function. It takes the form
=IF(condition, true_value, false_value)
In particular, the cell contining the function will take on one of two
values depending on the condtion. The IF() function cannot put a value
into a different cell, as it sounds like you want it to do.

You can nest IF() functions (make the true_value or the false_value
another IF() function, to get more than two alternatives, but the result
of the nested IF()'s again appears in the cell that contains the formula.

If you want to values to appear in multiple cells based on conditions,
you either have to put appropriate IF() functions in each of those
cells, or write a macro.

Jerry

 

[EV]

Hi, I'm sorry, I think you misunderstood, I guess my post was unclear.
(I didn't mean to post twice, but the first post looked like it ended
up being a response to someone elses' post, so I re-posted as a
separate question.)

What I meant to say was that this formula has to be based in ONE Cell
(O7)
From there it needs to look up data in different cells (and different
worksheets) for the following items:

If (N7) equals "YES" then, look at (G7), If (G7) equals $0 - $25, then
multiply that amount by (otherworksheet!I27) (These numbers are on
another worksheet in the workbook)

but

If (G7) equals $25.01 - $1,000 then multiply initial $25 by
(otherworksheet!I27)
plus multiply the remaining balance ($25.01 to $1,000) by
(otherworksheet!I28)

but

If (G7) equals $1,000.01 or more, then multiply initial $25 by
(otherworksheet!I27) plus, multiply of the initial $25.01 - $1000
by(otherworksheet!I28) plus, multiply the remainder by
(otherworksheet!I29) of the remaining balance ($1000.01 and up)

else,

If (N7) equal "No" then, "0"



I hope that was more clear.

Thanks for any advice.

Kind regards,
Elena

 

[Jerry W. Lewis]

Just nest the IF() functions to deal with each case in turn. One
implementation (assuming that G7 cannot be negative) would be

=IF(N7="YES",IF(G7<=25,G7*otherworksheet!I27,
IF(G7<=1000,25*otherworksheet!I27+(G7-25)*otherworksheet!I28,
25*otherworksheet!I27+(975)*otherworksheet!I28+(G7-1000)*otherworksheet!I29
)),0)

Jerry