Forum Discussion
Rounding with an IF formula
I have a formula for an invoice that appears not to be calculating properly due to a rounding issue. here is the original formula
=IFNA(IF(ISNUMBER(VALUE(LEFT([@GARMENT],2))),INDEX('STYLES CUSTOMER $$'!$E$3:$I$102,MATCH([@GARMENT],'STYLES CUSTOMER $$'!$A$3:$A$102,0),MATCH(IFS([@4XL]<>"",$L$18,[@3XL]<>"",$K$18,[@2XL]<>"",$J$18,OR([@XL]<>"",[@L]<>"",[@M]<>"",[@S]<>"",[@XS]<>"",[@YTH]<>""),"YTH-XL"),'STYLES CUSTOMER $$'!$E$2:$I$2,0)),VLOOKUP([@Column1],$S$18:$Y$47,7,0)),"")
so i added the "round function like this. ("round" At start and then ",2" at the end.
=ROUND(IFNA(IF(ISNUMBER(VALUE(LEFT([@GARMENT],2))),INDEX('STYLES CUSTOMER $$'!$E$3:$I$102,MATCH([@GARMENT],'STYLES CUSTOMER $$'!$A$3:$A$102,0),MATCH(IFS([@4XL]<>"",$L$18,[@3XL]<>"",$K$18,[@2XL]<>"",$J$18,OR([@XL]<>"",[@L]<>"",[@M]<>"",[@S]<>"",[@XS]<>"",[@YTH]<>""),"YTH-XL"),'STYLES CUSTOMER $$'!$E$2:$I$2,0)),VLOOKUP([@Column1],$S$18:$Y$47,7,0)),""),2)
It is functioning and rounding like I need it to, however, the problem is when there is a blank in the cell, it is showing a #VALUE error. How do I make the cell blank like it was prior to adding the "round" function? I have tried putting the round function in a couple different places but its not working. where is the proper place. Hopefully this makes sense.
How about
=IFERROR(ROUND((IF(ISNUMBER(VALUE(LEFT([@GARMENT],2))),INDEX('STYLES CUSTOMER $$'!$E$3:$I$102,MATCH([@GARMENT],'STYLES CUSTOMER $$'!$A$3:$A$102,0),MATCH(IFS([@4XL]<>"",$L$18,[@3XL]<>"",$K$18,[@2XL]<>"",$J$18,OR([@XL]<>"",[@L]<>"",[@M]<>"",[@S]<>"",[@XS]<>"",[@YTH]<>""),"YTH-XL"),'STYLES CUSTOMER $$'!$E$2:$I$2,0)),VLOOKUP([@Column1],$S$18:$Y$47,7,0)),2),"")
Thanks for trying. It didn't work. says theres a problem with the formula. Looks like there was a couple things removed from the formula....was that intentional or is that by chance the error?
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
