If function

D

dunmore83

Well-known Member
Joined
Aug 24, 2011
Messages
540
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
How about:

=IF(D6="JPY",IF(F6="BUY",(G6-H6)*100,(H6-G6)*100),IF(G6="BUY",(H6-I6)*10000,(I6-H6)*10000))
 
Upvote 0
Thanks for you reply. These do not seem to work.

To further clarify cell D6 will have varying text

i.e. "GBP / CHF" or "AUD / JPY"

The distinction needs to be made when the text "JPY" appears within the cell.

Also when the text "JPY" appears in cell D6 then I need cells H6 I6 K6 to be automatically be 3 decimal cells.

Conversley if cell D6 contains any text other then "JPY" then cells H6 I6 K6 need to be automatically 5 decimal cells.

Do you know how I can do this?

Thanks.
 
Upvote 0
An option would be to use Conditional Formatting, set the required cells

=D6="JPY" set format for 3 d.p.
=D6<>"JPY" set format for 5 d.p.
 
Upvote 0
Thanks.

Could you please walk me through that?

Which rule type do I select in the Conditional Formating & how do I set it to either 5 decimal places or 3 decimal places?
 
Upvote 0
Select H6 to K6

Select Conditional Formating, Select New Rule, Select Use Formula, Insert =D6="JPY", Click Format, Select Number Tab, Number Option and change to 3 d.p.

Select New Rule, Select Use Formula, Insert =D6<>"JPY", Click Format, Select Number Tab, Number Option and change to 5 d.p.

This should do the job
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Highlighting duplicate consecutive cells by row
Replies
2
Views
331
kvsrinivasamurthy
K
T
  • Solved
A more elegant countifs criteria than "$B6-$B5/1<0" across 50 columns
Replies
3
Views
267
tekram
T
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
140
ipbr21054
I
Romano_odK
  • Question
Copy the values but not overwrite when its not filled
Replies
0
Views
124
Romano_odK
Romano_odK
Romano_odK
  • Question
IFS formula doesn't work
Replies
4
Views
453
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,058
Messages
6,128,538
Members
449,456
Latest member
SammMcCandless

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top