Excel Functions - IF?

[LaDonna Brooks]

Is there a formula that I could use if it says EXCL (meaning exclude) to have
it enter that amount in another cell as a minus?


msladona

 

[FSt1]

hi
you are a bit sketchy on details so i made some up.
A5=50
B5=EXCL
in C5 enter this formula
=if(B5="EXCL",A5*-1,0)

if that is not how your data is layed out, post back with more details.

Regards
FSt1

 

[MyVeryOwnSelf]

Is there a formula that I could use if it says EXCL (meaning exclude)

to have it enter that amount in another cell as a minus?

Here's one way, assuming
- the "amount" is in A1, and
- EXCL may or may not be in A2.

Put this in "another cell"
=IF(A2="EXCL", -ABS(N(A1)),"XXX"))

Replace "XXX" by what should be in "another cell" in case A2 does not say
EXCL (the original post didn't specify).

I interpreted "as a minus" to mean "a negative number whatever the sign of
A1."

If A1 contains text instead of a number, the formula returns zero; that's
what the N() does.

Modify to suit.

 

[LaDonna Brooks]

Yes this formula worked perfect. I have an additional question - if B1-B5
are EXCL and B6-B10 are not is there a way to show that. I tried the formula
putting B1:B5 but it did not work - can you tell me what I am doing wrong?
This is how I entered the formula:

=IF(B1:B5="EXCL",A1:A5*-1,0)

This was entered into C11. Thanks for your help.

 

[MyVeryOwnSelf]

if B1-B5 are EXCL and B6-B10 are not is there a way to show that. I

tried the formula putting B1:B5 but it did not work - can you tell me
what I am doing wrong? This is how I entered the formula:

=IF(B1:B5="EXCL",A1:A5*-1,0)

Maybe this would help:
=IF(AND(
COUNTIF(B1:B5,"EXCL")=5,
COUNTIF(B6:B10,"EXCL")=0), A5*-1, 0)