Excel IF Function

  • Thread starter Thread starter Serenity99
  • Start date Start date
S

Serenity99

I would like some help with a formula. Here's the situation -

Cell A5 - dollar amount
Cell D5 - either blank or has x in it

I would like a formula that says if D5 has an x in it, then change the
dollar amount in A5 to a NEGATIVE figure. How do I do that?
 
Given your setup, put this in cell E5

=IF(D5="x",-A5,A5)

Be sure that whatever you use for the marker in D5 is identical wit
the marker in the " " in the formula, i.e. X in D5, and x in formul
will not work properly. It has to be x in D5 and x in the formula.

Then copy this formula down
 
Will it make a difference if someone puts an X as opposed to an x, i.e
upper case vs lower case? If so, I am wondering how I can build tha
into the form so it is always in lower case
 
I just noticed - there is no E5. What I want it to do is to CHANGE th
positive/negative in A5 if D5 has an X in it (putting the correcte
number back in A5
 
Well, it will be a little difficult to change, because you can't have a
number and a formula using that number in the same cell. That is why I
added the formula in E5.

You can use Data Validation to restrict what they put into D5. Select
cell D5, then

Data > Validation

Then from the drop down list, choose List, and in the Source box, put
whatever you want, i.e. x. Then click OK.

Now whenever you try to enter a value that is not correct, you get a
warning. To help them even more, when the Valdiation dialog box is
open, select the last tab, Error Alert. Put a title for it (Exact entry
required) then in the message box put "You must enter a small x"
(without the quote marks).

Then copy down colmn D, and this validation will work.
 
I think I know a way around this which gets me out of having to change
positive to a negative figure. Please let me know if this would work.

Basically, three cells would be involved:

A5 - expense amount cell
D5 - cell that would or would not have the x, meaning reimbursable
Total cell on expense report


I could put a formula in the Total Cell that basically says

If D5 has no X, then Amount in Total Cell stays as is, but if D5 has a
X in it then the Total Cell field will now equal original Total Cel
amount less the amount in cell A5.

What would be the formula for that
 
I think you need four cells.

A5 - expense amount cell
D5 - cell that would or would not have the x, meaning reimbursable
E5 -original total
F5 - final total

In F5, put this formula:

=IF(D5="x",E5-A5,E5)

HTH
Basically, three cells would be involved:

A5 - expense amount cell
D5 - cell that would or would not have the x, meaning reimbursable
Total cell on expense report


I could put a formula in the Total Cell that basically says

If D5 has no X, then Amount in Total Cell stays as is, but if D5 ha
an X in it then the Total Cell field will now equal original Tota
Cell amount less the amount in cell A5.

What would be the formula for that? [/B
Click to expand...
 
Back
Top