Creating a formula to calculate a number, depending on the data validation status

[Far_Gone]

Hi there, I've been wrestling with this problem all afternoon and not getting anywhere.

Attached is an e.g. of the spreadsheet that's driving me to bang my head on my desk.

So referring to the spreadsheet, I am trying to create a formula to work in cells I3-K3. At the moment a static formula is in there. However, I need this formula to return a result against the below variables:

IF C3 is "inprogress" or "notstarted", to refer to B3. Then calculate B3*F3.
OR
IF C3 is "finished" or "cancelled", to refer to D3. Then calculate D3*F3.

Normally I'm ok with IF formulas, but I think this one, because the calculation is based on a text-based variable, it needs something else...

Hope that all makes sense?

Thanks in advance!

[FDibbins]

Hi and welcome to the forum

If you only have the 4 options to test against (2 at a time), use this...
=IF(OR(C3="inprogress",C3="notstarted"),B3*F3,D3*F3)

if you have more than those 2 pairs, then add on to this as needed, the concept should be clear though...
=IF(OR(C3="inprogress",C3="notstarted"),B3*F3,IF(OR(C4="finished",C4="cancelled"),D3*F3,"whatever else you want to add"))

I would ask though...is it possible for C3 to be "inproress" and for D3 to be "cancelled"? or any combo of the 4?

[Far_Gone]

Hi there, thanks so much for this!

The final version of the formula is below & it's working brilliantly:

=IF(OR(C3="inprogress",C3="notstarted",C3="finished",C3="cancelled"),$B3*F3,$D3*F3)

At the risk of sounding silly, getting this sorted has made my day :-D

[Far_Gone]

Ok, new day = fresh eyes!

The final formula is actually:

=IF(OR($N5="inprogress",$N5="notstarted"),$L5*Q5,IF(OR($N5="finished",$N5="cancelled"),$O5*Q5))

Thanks again!

[Kevin UK]

Hi Far_Gone

Try:
=IF(OR($C3={"inprogress","notstarted"}),$B3*F3,IF(OR($C3={"finished","cancelled"}),$D3*F3,""))