Forum Discussion

Gemma_Strachan's avatar
Gemma_Strachan
Copper Contributor
Dec 13, 2023

HELP! IF formula with Multiple Considerations

Hello,

 

I am looking for some help on typing a formula which takes multiple considerations.

 

I have 3 cells which I am looking up across, average, Band and HOD - some of the information is N/A but I ultimately need to write the following:

IF cell A >cell B, Use Cell A (but I need a cavate if the cell has N/A or 0), then I need if Cell A > than Cell C, Use Cell C (again cell C contains N/A'S) and if Cell  B is > than Cell C - use cell C

 

I have spent 2 hours on this and would greatly appreciate any help

 

thanks

Gemma

Formulas and Functions
  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    Dec 13, 2023

    Gemma_Strachan 

     

    ... average, Band and HOD ...

    ... some of the information is N/A ...

    If cell A >cell B, Use Cell A (but I need a cavate if the cell has N/A or 0), then I need if Cell A > than Cell C, Use Cell C (again cell C contains N/A'S) and if Cell  B is > than Cell C - use cell C

    It's not easy to determine what you're looking for from this description.

    Please could you:

    1. Clarify how many formulas are you trying to create? Is it just one, or is it one per condition?

    2. Add an image showing an example of the three values and the output you expect to see (this doesn't need to be using a formula, just type what you want to see

    3. What exactly should happen if a cell is #N/A or zero? 

     

    Thanks

    • Gemma_Strachan's avatar
      Gemma_Strachan
      Copper Contributor
      Dec 13, 2023

      flexyourdata 

       

      Hello,

      Sorry

      Its one formula that basically I need it to come out with a specific number from the 3
      A = 100
      b = 200
      c = 300

      If a Is higher than B I use A, but if C is higher than A then I use C - but if either Cell has an N/A (namely A and C) I don't 

      know how to do the calc so It shows the number

      A = N/A or value
      B = 100
      C = n/a or value

       

      Hopefully this image portrays what I am trying to do with the 3 examples

       

      Thank you

      • flexyourdata's avatar
        flexyourdata
        Iron Contributor
        Dec 13, 2023

        Gemma_Strachan 

         

        Ok, I think I see now. 

        You have a missing condition in your IFS function call, namely what to output in case none of your conditions are true. This is the same as when D is equal to or less than B. 

        Replace that text in this formula with your default value (perhaps zero?).

         

        =IFS(
  ISNA(B2),C2,
  B2>C2,B2,
  ISNA(D2),C2,
  D2>B2,D2,
  TRUE,"Output when D is equal to or less than B"
)

         

  • Gemma_Strachan's avatar
    Gemma_Strachan
    Copper Contributor
    Dec 13, 2023
    I basically need it to come out with a specific number from the 3
    A = 100
    b = 200
    c = 300

    If a Is higher than B I use A, but if C is higher than A then I use C - but if either Cell has an N/A (namely A and C) I dont know how to do the calc so It shows the number

Resources