IF statement

[AussieWayne]

Hi guys I have a simple one for you I think. I am just not familiar enough to make it work although I think I am close but obviously not close enough.

I have 3 columns, H has letters and I has $ amounts and L is blank

H6......................I6.......... L6
AUD .................$123........
ABC .................$ 58
XYZ .................$224........

Where the test equals AUD I wish to show the $ value of I in L
When it does not equal AUD then L is to be blank

This is what i tried inserting into cell L6 but it is not working
=IF(H6="aud","I6","0")

Please someone show me the error of my ways.
Thank you in advance

 

[fhqwgads]

i think try remove the "" around the cell reference, otherwise you just get the name of the cell
so change
=IF(H6="aud","I6","0")
to
=IF(H6="aud",I6,"0")

 

[vcoolio]

Hello AussieWayne,

Try the following modification to your formula:-

=IF(H6="AUD",I6,[COLOR=#ff0000]""[/COLOR])

Just removing the 0 from "0" should suffice.

I hope that this helps.

Cheerio,
vcoolio.


..........and of course, no quotation marks around I6.

 

[AussieWayne]

Hi guys thank you both very much as I have success using both suggestions.

I was close but that is never good enough. One has to be spot on so I turned to the experts after I could not find the answer myself.
Best to all
AussieWayne

 

[vcoolio]

Cheers Wayne.
Glad we could help.

Cheerio,
vcoolio.

 

[AussieWayne]

Hi guys to obtain meaningful results I now find that I actually need to insert a second test in Column B to make it True.
So I have
B6............H6......................I6.......... L6
Sell..........AUD .................$123........
Buy..........ABC .................$ 58
Sell..........XYZ .................$224........
and I need to have ONLY the records that have BOTH "Sell" and "AUD" in their respective columns.
I guess I need a nesting AND condition and have tried a few things but cant quite work it out.

Can somebody advise what do I need to add this in please?
Thanking you again.
AussieWayne

 

[vcoolio]

Hello again AussieWayne,

This should do the task for you:-

=IF(AND(B6="Sell",H6="AUD"),I6,"")

Cheerio,
vcoolio.

 

[AussieWayne]

Thank you so much for your help vcoolio that certainly did the trick.
Best wishes
AussieWayne

 

[vcoolio]

Cheers Wayne.