Excel replacing formula with "=#VALUE!"

%3CLINGO-SUB%20id%3D%22lingo-sub-1674371%22%20slang%3D%22en-US%22%3EExcel%20replacing%20formula%20with%20%22%3D%23VALUE!%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674371%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20the%20following%20formula%20in%20a%20spreadsheet%3A%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX('Data%20Q2%202020'!%24A%3A%24A%2CSMALL(IF('Data%20Q2%202020'!%24H%3A%24H%3D%24K%2430%2CROW('Data%20Q2%202020'!%24A%243%3A%24A%243000))%2CROW('Data%20Q2%202020'!%2438%3A%2438))-2%2C1)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20is%20working%20very%20well%20as%20intended%2C%20but%20then%20Excel%20replaces%20the%20above%20formula%20with%3A%3C%2FP%3E%3CTABLE%20width%3D%22119%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22119%22%3E%3D%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eafter%20a%20certain%20number%20of%20rows%20are%20filled%20with%20the%20intended%20formula%20(the%20first%20formula).%26nbsp%3B%20The%20VALUE%20change%20does%20not%20take%20place%20immediately%2C%20but%20after%20I've%20saved%20the%20spreadsheet.%26nbsp%3B%20I'm%20using%20the%20first%20formula%20for%20a%20large%20(28mg)%20spreadsheet%20to%20report%20values%20for%20over%20100%20clients%20in%20the%20past%20five%20quarters.%26nbsp%3B%20Again%2C%20all%20formulas%20are%20working%20well%20and%20are%20stable%20when%20I%20save%20the%20file.%26nbsp%3B%20It's%20only%20upon%20opening%20the%20file%20that%20my%20original%20formulas%20are%20changed%20to%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22119%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22119%22%3E%3D%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26nbsp%3B%3C%2FP%3E%3CP%3EWayne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1674371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682354%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20replacing%20formula%20with%20%22%3D%23VALUE!%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796077%22%20target%3D%22_blank%22%3E%40Wayne2016%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20you%20achive%20with%20this%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20This%20part%20of%20the%20formula%20is%20comparing%20column%20with%20a%20cell%20value%20which%20will%20return%20an%20array%20of%20Trues%20and%20Falses.%20And%20if%20it%20is%20true%20it%20will%20return%20ROW(...)%20but%20if%20it%20is%20false%20it%20will%20return%20FALSE%20which%20is%20converted%20to%200%20for%20calculations.%20These%20array%20comparing%20won't%20work%20as%20expected%20in%20an%20IF%20formula.%3C%2FP%3E%3CP%3E%3DIF('Data%20Q2%202020'!%24H%3A%24H%3D%24K%2430%2CROW('Data%20Q2%202020'!%24A%243%3A%24A%243000))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20This%20will%20always%20return%2038%3C%2FP%3E%3CP%3EROW('Data%20Q2%202020'!%2438%3A%2438)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20SMALL(...)-2%20will%20be%20converted%20in%20to%20SMALL(0%2C38)-2%20which%20will%20then%20evaluated%20to%20-2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20INDEX(...%2C-2)%20will%20be%20evaluated%20as%20%23VALUE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683208%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20replacing%20formula%20with%20%22%3D%23VALUE!%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683208%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796077%22%20target%3D%22_blank%22%3E%40Wayne2016%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHere%20you%20can%20find%20information%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CH1%20id%3D%22toc-hId-530217677%22%20id%3D%22toc-hId-530217677%22%3EHow%20to%20fix%20the%20%23VALUE!%20error%3C%2FH1%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fhow-to-fix-the-value-error%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fhow-to-fix-the-value-error%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20third-party%20products%20that%20these%20article%20discusses%20are%20manufactured%20oe%20Informed%20by%20companies%20that%20are%20independent%20of%20me.%20I%20makes%20no%20warranty%2C%20implied%20or%20otherwise%2C%20about%20the%20performance%20or%20reliability%20of%20these%20products.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20%3C%2FFONT%3Emembers%20reading%20%3CFONT%20size%3D%222%22%3Ehere.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I'm using the following formula in a spreadsheet:

=IFERROR(INDEX('Data Q2 2020'!$A:$A,SMALL(IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000)),ROW('Data Q2 2020'!$38:$38))-2,1),"")

 

This formula is working very well as intended, but then Excel replaces the above formula with:

=#VALUE!

after a certain number of rows are filled with the intended formula (the first formula).  The VALUE change does not take place immediately, but after I've saved the spreadsheet.  I'm using the first formula for a large (28mg) spreadsheet to report values for over 100 clients in the past five quarters.  Again, all formulas are working well and are stable when I save the file.  It's only upon opening the file that my original formulas are changed to 

=#VALUE!

 

Any suggestions?

 

Thank you, 

Wayne

2 Replies
Highlighted

@Wayne2016 

What do you achive with this formula?

 

1. This part of the formula is comparing column with a cell value which will return an array of Trues and Falses. And if it is true it will return ROW(...) but if it is false it will return FALSE which is converted to 0 for calculations. These array comparing won't work as expected in an IF formula.

=IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000))

 

2. This will always return 38

ROW('Data Q2 2020'!$38:$38)

 

Your SMALL(...)-2 will be converted in to SMALL(0,38)-2 which will then evaluated to -2.

 

Then INDEX(...,-2) will be evaluated as #VALUE

Highlighted

@Wayne2016 

Here you can find information

 

How to fix the #VALUE! error

https://exceljet.net/formula/how-to-fix-the-value-error

 

The third-party products that this article discusses are manufactured oe Informed by companies that are independent of me. I makes no warranty, implied or otherwise, about the performance or reliability of these products.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.