I have this big LET
=LET(paper; $A5; sellDate; $M5; qtySold; $B5;initQty; IFERROR(XLOOKUP($A5; $A$50:$A$80; $B$50:$B$80); 0);initCost; IFERROR(XLOOKUP($A5; $A$50:$A$80; $D$50:$D$80); 0);buyQty; SUMIFS($B$5:$B$45; $A$5:$A$45;paper; $F$5:$F$45;"<"&sellDate); sellQtyBefore; SUMIFS($B$5:$B$45; $A$5:$A$45; paper; $M$5:$M$45; "<"&$M5); costBefore; initCost + SUMIFS($E$5:$E$45; $A$5:$A$45; $A5; $F$5:$F$45; "<"&$M5); qtyBefore; initQty + buyQty - sellQtyBefore; avgPrice; IF(qtyBefore=0; 0; costBefore / qtyBefore); IF($F5=""; N5 - qtySold * avgPrice; IF(qtyBefore < 0; avgPrice*qtySold - $E5;0)))
When i try to calculate it it gives the #VALUE! error. I check with the function wizard and the first error appears in the calculation of SUMIFS($B$5:$B$45; $A$5:$A$45;paper; $F$5:$F$45;"<"&sellDate). It gives an #NAME! error. If I try to calculate =SUMIFS($B$5:$B$45; $A$5:$A$45;$A5; $F$5:$F$45;"<"&$M5) in another cell it works. I do not understand what is the problem with the calculation in the LET. I would appreciate any help.
Please help us to help you…and attach an exsample.ods with concrete data!
2 Likes
Missing dates in column F.
You mean it works in any cell but but it does not work inside the LET function?
.
If there is no date in column F, there is an error in column V. Enter a date in F and the error is gone.
I can put dates in the column F only when a buy happens. But i stil don not understand why the formula works outside the LET function but not inside it. Thanks.