Forum Discussion

Mattafact's avatar
Mattafact
Copper Contributor
Jan 25, 2025

=IF Formula Woes

Hello All!

I'm working on a spreadsheet with a cell (C2) containing a drop-down menu. 

I'm trying to create a formula in another cell (D2) dependent on the selected item in the drop-down menu.  I was successful at doing this with the first menu item ("14 Day"), but when I tried to enter the formatting for the second menu item ("21 Day"), I got the dreaded #VALUE! error.  

I've attached some pics that hopefully explain what I was trying to achieve.  

The red section of the formula below gives me the error.

=IF(C1="14 Day",SUM(B2*14))*C2, IF(C1="21 Day",SUM(B2*21))*C2

Any help or advice would be much appreciated!  Thank you in advance!

 

excel

3 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 28, 2025

    As variant

    =IF( $C$1="14 Day", 14,
 IF( $C$1="21 Day", 21, NA() ) )
 *B2*C2

    or slightly modified for legacy Excel HansVogelaar formula

    =LEFT($C$1,2)*B2*C2
  • NnyiimockBitanyanmi's avatar
    NnyiimockBitanyanmi
    Brass Contributor
    Jan 28, 2025

    I could see you really do not need a new formula maybe. Do some edits as below:
    your formula =IF(C1="14 Day",SUM(B2*14))*C2,IF(C1="21 Day",SUM(B2*21))*C2, has absolute reference issues, there is also some issue with parenthesis. Compare with mine here,=IF($C$1="14 Day",SUM(B2*14)*C2,IF(C1="21 Day",SUM(B2*21)*C2)) and it will work perfectly i think. Maybe it is important for you, if not, why should you use 'Merge and Center', i am just been curios here😄.

Resources