Forum Discussion
Excel Formula help
I am trying to write an IF AND statement that has multiple parts, kind of long. 1st it will look if a date cell (J12) is blank, if so then Cell (I12) is "NO", then it will check if cell (H12) is <-4999 and if cells (H12/D12)<-10%, if both are true then cell (I12) is "NEEDS ADDITIONAL APPROVAL", If 1 or both are false then cell (I12) is "YES".
Currently my formula is
=IF(J13="","NO",IF((AND(H13<-4999,H13/D13<-10%)),"NEEDS ADDITIONAL APPROVAL","YES"))
and i am getting an inconsistent formula error.
Cell values J12 Feb-18, H12 (4,500) (which is calculated by =G13 15,000-(F13 19,500+E13 Blank), D12 15,000.
I attached the portion of the doc I'm running into issues on as well.
Any help would be greatly appreciated.
Hi Nick,
In the example that you attached, I did not find any error, the formula is going well!
=IF(J13="","NO",IF((AND(H13<-4999,H13/D13<-10%)),"NEEDS ADDITIONAL APPROVAL","YES"))
Let's break the formula into parts like this:
J13=""
The result is False since the cell J13 isn't blank.
H13<-4999
The result is False since the value of H13 is -4500 which really NOT less than -4999.
H13/D13<-10%
The result is True since the result of H13/D13 is -30% which is definitely less than -10%.
The final result of AND is False because it has at least one logical test ended up in False.
After that, the final result of the formula is YES.
4 Replies
Hi Nick,
In the example that you attached, I did not find any error, the formula is going well!
=IF(J13="","NO",IF((AND(H13<-4999,H13/D13<-10%)),"NEEDS ADDITIONAL APPROVAL","YES"))
Let's break the formula into parts like this:
J13=""
The result is False since the cell J13 isn't blank.
H13<-4999
The result is False since the value of H13 is -4500 which really NOT less than -4999.
H13/D13<-10%
The result is True since the result of H13/D13 is -30% which is definitely less than -10%.
The final result of AND is False because it has at least one logical test ended up in False.
After that, the final result of the formula is YES.
Thank you for the quick response, and verifying it works, it just bugs me that the inconsistent formula error keeps popping up on the document, any thoughts on that besides just hitting ignore error?
Hi Nick,
This is expected error because the formula in the cell I13 is unique from the other formulas in the same column!
This is the meaning of (Inconsistent Formula).
This error has no relation to the result of the formula and will never affect it, the formula will still calculate the result correctly!
Just copy the formula to the above and down to get rid of this error (unify the formula).
For example, cell I12 has this formula which is different:
=IF(J12="","NO",IF(H12<-4999,"NEEDS ADDITIONAL APPROVAL DUE TO COST OVERRUNS",IF(H12/D12<-10%,"NEEDS ADDITIONAL APPROVAL DUE TO COST OVERRUNS","YES")))
