SOLVED

IF to check multiple cells and formula for each depending on result

New Contributor

Hi all,

 

So the use for this is a trade journal. You take a trade/position, it has 3 take profits which close a % of the trade. The cell I'm trying to create is a Stop loss. How big the position currently is depends on which take profit level was hit last, if any.

 

I'm trying to create IF function that scans 3 cells

if empty perform a simple formula

if not empty move onto next cell

if final cell is empty perform a simple formula, if final cell is not empty provide alternate formula

 

What I have so far, no idea if I'm close I'm not great with excel so I use google searches to find results.

 

=IF(I2="",*1,IF(K2="",*0.8,IF(M2="",*0.4,"",*0.2,)))

 

As you can see the final IF function has two formulas, my little understanding of it suggests the second "" means 'Not blank' to which it goes on to perform *0.2.

 

 

Thanks a lot for you help. I hope it's clear!

 

 

 

5 Replies

@Budgen 

I strongly recommend using the function arguments menu when getting started with logical formulas (It's a big help with IF-OR and IF-AND, especially). It will help you step through the arguments and avoid errors.

Patrick2788_0-1667417151488.png

As you can see from above, the first logical test results in TRUE, so Excel will go to the Value if True (Box) but it doesn't understand *1 because the * is a multiplier.  

 

What would you like to do for these bolded parts? Do you need to multiply those percentages by the value in a cell?

 

=IF(I2="",*1,IF(K2="",*0.8,IF(M2="",*0.4,"",*0.2,)))

Brilliant break down I appreciate you taking the time, I didn't even know that existed. Bear with me having such little understanding.
That's correct, * all by the value in cell O2.
best response confirmed by Budgen (New Contributor)
Solution

@Budgen 

 

Here's the formula:

=IF(I2="",O2*1,IF(K2="",O2*0.8,IF(M2="",O2*0.4,O2*0.2)))

 

You might also see it written like:

=IF(ISBLANK(I2),O2*1,IF(ISBLANK(K2),O2*0.8,IF(ISBLANK(M2),O2*0.4,O2*0.2)))
That's spot on. Thanks for that Patrick, much appreciated!