Forum Discussion

Copper Contributor

Apr 13, 2018

Solved

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 <-49...

Error Needing Fix.xlsx15 KB

excel

Formulas and Functions

Haytham Amairah
Apr 13, 2018
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.

Haytham Amairah

Silver Contributor

Apr 13, 2018

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.

Nick Wagener
Copper Contributor
Apr 13, 2018
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?
FullDocError.xlsx21 KB
- Haytham Amairah
  Silver Contributor
  Apr 13, 2018
  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")))
  - Nick Wagener
    Copper Contributor
    Apr 13, 2018
    Makes sense. Thank you very much for the speedy replies.