Nested If Formula #VALUE! Error!

Dear Experts,

Need your valuable support to understand and clear below stated issue!

The Reason for me to post this thread on our forum is…. I am into supply chain team in one of the big Manufacturing Unit and handling more than 10,000 items. I am suppose to have a close track and present detailed summary like

What and all the parts are:-

1. Available with us (On Hand)
2. Date confirmed by supplier is Okay
3. Critical (or) Very Critical
4. Transit Etc………………..,

For all 10K items, manually If I go and check above said category and update the status for each item. It may take more than a Day to complete this task.

Thus, with my available knowledge in Excel; I designed the Nested If function. Which keep gets updated automatically based on inputs (like Receipts date, Qty, Etc.,). Please open the attached file for your further Reference.

The formula which is there in “Column M” works extremely well except one condition. That is, when the “Receipts ~ 1” (Column C) is having any Text it provides #VALUE! Error.

Can you please help me get this issue cleared? so that it will help me a lot!

Immediate help is Really Appreciated, Looking forward great suggestion from your end!

Thanks & Regards,
Rajeshkumar R

you could wrap the whole thing into an iferror( which looks for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

so this is the formula you have
=IF(ETA_1="NR","NR",IF(SHORTAGE_1>=0,"On Hand",IF(ISBLANK(ETA_1),"ETA To Be Updated",IF(AND(SHORTAGE_1+ETA_1>=0,OR(REF_1="T",ETA_1="Receiving",ETA_1="ST",ETA_1="IGI")),"Transit",IF(OR(ETA_1="NO PO",REF_1="L"),"Okay",IF(AND(SHORTAGE_1+QTY_1>=0,OR(ETA_1<=TARGET_1)),"Okay",IF(REF_1="E","Very Critical",IF(AND(SHORTAGE_1+QTY_1>=0,OR(ETA_1>TARGET_1,ETA_1="TBA",ETA_1="IGI Rejected")),"Critical",IF(AND(SHORTAGE_1+QTY_1<0,SHORTAGE_1+QTY_1+QTY_2>=0,ETA_2<=TARGET_1),"Okay",IF(AND(SHORTAGE_1+QTY_1<0,SHORTAGE_1+QTY_1+QTY_2>=0,OR(ETA_2>TARGET_1,ETA_2="TBA")),"Critical",IF(AND(SHORTAGE_1+QTY_1<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3>=0,ETA_3<=TARGET_1),"Okay",IF(AND(SHORTAGE_1+QTY_1+QTY_2<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3>=0,OR(ETA_3>TARGET_1,ETA_3="TBA")),"Critical",IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4>=0,ETA_4<=TARGET_1),"Okay",IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4>=0,OR(ETA_4>TARGET_1,ETA_4="TBA")),"Critical",IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4+QTY_5>=0,ETA_5<=TARGET_1),"Okay",IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4+QTY_5>=0,OR(ETA_5>TARGET_1,ETA_5="TBA")),"Critical",IF(SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4+QTY_5<0,"ETA Qty Not Enough")))))))))))))))))

now add

=IFERROR (

your formula

, "what to do if an error returned")

see attached spreadsheet with iferror added

if its got text in the field then you are not goingto be able to do a calculation on that field
if you want the word "critical" just replace in my formula

"what to do if an error returned"

with
"Critical"

so if the whole formula you have results in #Value this will resolve that
OR

do you think there is an error in your formula anyway

=IFERROR(


IF(ETA_1="NR","NR",

IF(SHORTAGE_1>=0,"On Hand",

IF(ISBLANK(ETA_1),"ETA To Be Updated",

IF(AND(SHORTAGE_1+ETA_1>=0,OR(REF_1="T",ETA_1="Receiving",ETA_1="ST",ETA_1="IGI")),"Transit",

IF(OR(ETA_1="NO PO",REF_1="L"),"Okay",

IF(AND(SHORTAGE_1+QTY_1>=0,OR(ETA_1<=TARGET_1)),"Okay",

IF(REF_1="E","Very Critical",

IF(AND(SHORTAGE_1+QTY_1>=0,OR(ETA_1>TARGET_1,ETA_1="TBA",ETA_1="IGI Rejected")),"Critical",

IF(AND(SHORTAGE_1+QTY_1<0,SHORTAGE_1+QTY_1+QTY_2>=0,ETA_2<=TARGET_1),"Okay",

IF(AND(SHORTAGE_1+QTY_1<0,SHORTAGE_1+QTY_1+QTY_2>=0,OR(ETA_2>TARGET_1,ETA_2="TBA")),"Critical",

IF(AND(SHORTAGE_1+QTY_1<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3>=0,ETA_3<=TARGET_1),"Okay",

IF(AND(SHORTAGE_1+QTY_1+QTY_2<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3>=0,OR(ETA_3>TARGET_1,ETA_3="TBA")),"Critical",

IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4>=0,ETA_4<=TARGET_1),"Okay",

IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4>=0,OR(ETA_4>TARGET_1,ETA_4="TBA")),"Critical",

IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4+QTY_5>=0,ETA_5<=TARGET_1),"Okay",

IF(AND(SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4<0,SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4+QTY_5>=0,OR(ETA_5>TARGET_1,ETA_5="TBA")),"Critical",

IF(SHORTAGE_1+QTY_1+QTY_2+QTY_3+QTY_4+QTY_5<0,"ETA Qty Not Enough"))))))))))))))))),

"Critical")

OR are you looking for someone to check out ALL the IFs you have

This part
=AND(SHORTAGE_1+ETA_1>=0,OR(REF_1="T",ETA_1="Receiving",ETA_1="ST",ETA_1="IGI"))

produces a #Value error

and its this part of the formula
=SHORTAGE_1+ETA_1>=0
as you are checking a text value for a number value , using >0

see attached
column O and P