HELP! IF formula with Multiple Considerations

Copper Contributor

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

7 Replies
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

@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

@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

 

Excel Help.PNG

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

 

Thank you

@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"
)

flexyourdata_0-1702505007267.png

 

@flexyourdata 

 

Thank you so much for this its super helpful, however that formula doesnt work if column A and C are both N/A then I need it to return colum B as there is always a value in this

 

Basically If my Average is greater than the band I use my Average, and where I have a figure in the HOD column i need to use this only if its greater than the average - and where I have no figures in A or C then it defaults to B

 

Does that make sense?

@Gemma_Strachan 

 

I think you've said:

 

If A > B and C > A then use C

If A is NA and C is NA then use B

If A > B and C <= A then use A

 

Is that correct? 

 

What about these cases?

A is NA and C > B (should this be "use C"?)

A <= B and C > A and C <= B (use C?)

A <= B and C is NA (not sure)

A > B and C is NA (use A?)

A = B = C (presumably use any of the three)

A = B and A < C (use C?)

And so on...

 

@Gemma_Strachan There's a lot of confusion caused by referring to variables A, B and C when the screenshot provided is using columns B, C and D. Plus, the logic is very hard to decipher... I've re-read it multiple times now, and I'm still not exactly sure what you mean.

 

Presuming you're dealing with positive numbers only, if the goal is to return the highest value between "Average" and "HOD", and ONLY return "Bandings" when BOTH "Average" and "HOD" are either #N/A or zero, try the following:

 

=IF(MAX(IFNA(B8, 0), IFNA(D8, 0))=0, C8, MAX(IFNA(B8, 0), IFNA(D8, 0)))

 

Or, if the LET function is available in your version of Excel:

 

=LET(
   v, MAX(IFNA(B8, 0), IFNA(D8, 0)),
   IF(v=0, C8, v)
)

 

However, if goal is to simply return the maximum value between all three columns while handling the #N/A errors, try the following:

 

=MAX(IFNA(B8:D8, 0))

 

Hopefully one of the above-mentioned methods is what you had in mind. Cheers!