Dec 13 2023 01:31 PM
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
Dec 13 2023 01:40 PM
Dec 13 2023 01:40 PM
... 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
Dec 13 2023 01:50 PM
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
Dec 13 2023 02:04 PM
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"
)
Dec 13 2023 11:02 PM
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?
Dec 14 2023 04:57 PM
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...
Dec 14 2023 08:27 PM
@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!