If function

[dunmore83]

Hi... sorry if this is complicated. I am not expecting it to be possible but would be very happy if there was a solution!!

Currently CELL J6 contains this formula;

=IF(F6="BUY",(G6-H6)*100,(H6-G6)*100)

This is based on cell F6 containing the text "BUY" or "SELL".

My situation is I need to expand this function by also searching cell D6. If cell D6 contains any text other than "JPY" then the formula in J6 needs to be;

=IF(G6="BUY",(H6-I6)*10000,(I6-H6)*10000)

If you need any more clarification please let me know.

Thanks

 

[dunmore83]

Thanks for the walk through...

Problem is it only works if cell D6 contains the exact text "JPY".

However it needs to work if it contains JPY within the cell i.e. cell D6 has many variations such as "AUD / JPY", "CAD / JPY", "GBP / JPY".... in these situations I need 3 decimal places.

All other situations I need 5 decimal places i.e. without the JPY such as "AUD / USD"

Much appreciated

 

[Skybluekid]

=if(and(f6="buy",d6<>"jpy"),if(g6="buy",(h6-i6)*10000,(i6-h6)*10000),(g6-h6)*100,(h6-g6)*100)

 

[dunmore83]

=if(and(g6="buy",d6<>"jpy"),if(g6="buy",(h6-i6)*10000,(i6-h6)*10000),(g6-h6)*100,(h6-g6)*100)

The above formula returns this errror....

You've entered too many arguments for this function. To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula.

Thanks for your help!

 

[Skybluekid]

Sorry copy and pasted too many times, Lol

Try this =IF(AND(G6="buy",D6<>"jpy"),IF(G6="buy",(H6-I6)*10000,(I6-H6)*10000),(G6-H6)*100)

 

[dunmore83]

I still need some help with this scenario...

Cell D6:D7 (merged) can contain the following text;

AUD / JPY
CAD / JPY
GBP / JPY
EUR / JPY
XAG / USD
XAU / USD
USD / CAD
AUD / USD
NZD / USD

Can I create a conditional formatting rule based on text within the cell (i.e. JPY or XAG or XAU)? Or does it have to be created based on all the text in the cell (i.e. "AUD / JPY")?

When JPY or XAG or XAU appears in cell D6:D7 then cell H6 needs to be 3 decimal places.

Whenever JPY or XAG or XAU does not appear in cell D6:D7 then cell H6 needs to be 5 decimal places.

Thanks for your help anyone!!!!

 

[dunmore83]

Can anyone please help??!!!

This formula does not seem to work & I can't work out why;

=IF(D6<>"jpy",IF(G6="buy",(H6-I6)*100,(I6-H6)*100),IF(G6="buy",(H6-I6)*10000,(I6-H6)*10000))


It neems to multiply by 1000 if these text entries are found in cell D6;

EUR / USD
GBP / USD
AUD / USD
NZD / USD
USD / CHF
USD / CAD
GBP / CHF
GBP / CAD
GBP / NZD
GBP / AUD
EUR / GBP
EUR / CHF
EUR / CAD
EUR / NZD
AUD / CHF
AUD / CAD
AUD / NZD
CAD / CHF

It needs to multiply by 100 if these text entries are found in cell D6;

USD / JPY
GBP / JPY
EUR / JPY
CHF / JPY
CAD / JPY
AUD / JPY
NZD / JPY
XAU / USD
XAG / USD

I would really appreciate it if someone could help me solve this one!

 

[dunmore83]

Can someone please help me!!!! This is so close to being solved.