Forum Discussion
HELP! IF formula with Multiple Considerations
... 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
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 = 300If 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 valueHopefully this image portrays what I am trying to do with the 3 examples
Thank you
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" )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?
