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

6 Replies

# Re: Rounding with an IF formula

=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),"")

# Re: Rounding with an IF formula

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?

# Re: Rounding with an IF formula

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.

# Re: Rounding with an IF formula

I CAN'T SEE HOW TO ADD THE FILE HERE. is there a way to just email it to you so the file isn't plastered all over the internet? i need to send all the tabs as there are links from other formulas on different tabs. sorry if confusing.

# Re: Rounding with an IF formula

You can send me a private message in this forum. You should be able to attach the file to the message.

# Re: Rounding with an IF formula

I have replied to your private message.