Greetings all,
Long time troller, first time poster.
I'm building a spreadsheet that tracks total product packaging weight by multiplying packaging weight per unit by the number of units sold. e.g. 10grams of HDPE per unit x 1000 units sold = 10kg of HDPE.
I have a large worksheet with all of the different products with their associated package weights. All of the simple SUM functions are in place, I just need to bring in the sales figures.
Sales numbers are on a separate sheet and I can bring them in using VLOOKUP. My 2 problems are #N/A's and negative numbers. I used an IF function to replace the negative numbers with zeros, but when I try to add a nested IF function to screen out the #N/A values, it messes it up.
Here is the function that I'm using:=IF(VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)<0,0, VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE))
How do I add to this function to also change N/A's to zero?
Thanks in advance.
Alan
Long time troller, first time poster.
I'm building a spreadsheet that tracks total product packaging weight by multiplying packaging weight per unit by the number of units sold. e.g. 10grams of HDPE per unit x 1000 units sold = 10kg of HDPE.
I have a large worksheet with all of the different products with their associated package weights. All of the simple SUM functions are in place, I just need to bring in the sales figures.
Sales numbers are on a separate sheet and I can bring them in using VLOOKUP. My 2 problems are #N/A's and negative numbers. I used an IF function to replace the negative numbers with zeros, but when I try to add a nested IF function to screen out the #N/A values, it messes it up.
Here is the function that I'm using:=IF(VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)<0,0, VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE))
How do I add to this function to also change N/A's to zero?
Thanks in advance.
Alan