Forum Discussion

Herbal_ingenuity's avatar
Herbal_ingenuity
Copper Contributor
Jul 20, 2023
Solved

Multiple If statements

What I am trying to do and it is WAY outside my skill set. I can't even begin to type a formula for it.   If B4="kgs" then A8 returns a value of .23   If B4="lbs" then A8 returns a value of .5  ...
Charting
excel
Formulas and Functions
office 365
Office Scripts
  • mathetes's avatar
    mathetes
    Jul 21, 2023

    Herbal_ingenuity 

     

    Depending on how many variable conditions are involved, this might also call for a two dimensional array, accessed by INDEX and MATCH.

     

    As Patrick2788 has already said, you could help us help you by articulating all of the conditions and consequences in as orderly fashion as possible (some tabular form would be ideal) and even better, accompany that by posting a copy of the workbook on OneDrive or GoogleDrive with a link here that grants access.

mathetes's avatar
mathetes
Gold Contributor
Jul 21, 2023

Herbal_ingenuity 

 

Depending on how many variable conditions are involved, this might also call for a two dimensional array, accessed by INDEX and MATCH.

 

As Patrick2788 has already said, you could help us help you by articulating all of the conditions and consequences in as orderly fashion as possible (some tabular form would be ideal) and even better, accompany that by posting a copy of the workbook on OneDrive or GoogleDrive with a link here that grants access.

  • Herbal_ingenuity's avatar
    Herbal_ingenuity
    Copper Contributor
    Dec 26, 2023
    I ended up with this...

    =IF(F8="","",IF(A8=0, IF($N$5="kgs", SUM((C8*T8)+E8+F8), SUM((C8*U8)+E8+F8)), IF($N$5="kgs", SUM((C8*T8)+E8+F8+0.23), SUM((C8*U8)+E8+F8+0.5))))

    T8 & U8 return values based on on what is in D8.
    There is probably something simpler, but I ended up there anyways. I have zero knowledge or background with Excel or computers other than how to surf them. I was able to make this formula through trial and error and google.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 27, 2023

      Herbal_ingenuity 

      Another notation could be

      =IF(F8="",
    "",
    E8+F8+ C8*IF($N$6="kgs",T8, U8) +
    (0.5 - 0.27*($N$6="kgs"))*(A8<>0) )
    • mathetes's avatar
      mathetes
      Gold Contributor
      Dec 26, 2023

      Herbal_ingenuity 

      I have zero knowledge or background with Excel or computers other than how to surf them. I was able to make this formula through trial and error and google.

       

      One of the best ways to learn Excel and its formulas and functions is through working on a situation you care about and being willing to do some trial and error.  It also helps to do some basic research. YouTube has some great introductory videos. A book like Excel for Dummies can be useful. Websites like ExcelJet also. 

      • Herbal_ingenuity's avatar
        Herbal_ingenuity
        Copper Contributor
        Dec 26, 2023
        mathetes
        That's pretty much what I have been doing. I appreciate the ExcelJet information. I will look into that as there are a few other dilemmas I am working on. I know they are all within the realm of possibilities, I just have to learn how to make them possible.

Resources