Need help on returning different answers based on different results - Comparing Sales

[ec4excel]

I have found a thread that can help for the first answer but was stuck on the next answer.

I want to compare sales for the first half year of 2011 and the first half year for 2012. After copying the data from 2 worksheets and removing duplicate rows, my first problem was:

Unable to return "0" when no sales was found for 2011 or 2012 when I use vlookup on the summary sheet (unable to use isblank, "", gives me error on "")

when the result returns a "#N/A" on the sales of either year, when I want to do a calculation on another column to compare the sales for the 2 years, it cannot subtract as the field with "#N/A" is not a number.

I had attached a sample file for reference.

I want to know from this list, what are the customers that has sales in 2011 but no sales in 2012 and no sales in 2011 but have sales in 2012.

[dogberry]

Hi

Try surrounding your formula with iferror to give a 0.

Chris

[ec4excel]

Hi

Try surrounding your formula with iferror to give a 0.

Chris

Dear Chris,

Thanks, this helps in my column C and D, but not not in E. Hoped someone can help on the returning result on column E. Else, the negative sign could means a decrease in sales but not losing a customer and the increase in sales in 2012 does not means a new customer added in 2012.

[Kevin UK]

Hi ec4excel
Try in E3 and copy down:

Please Login or Register  to view this content.

See attached

[ec4excel]

Dear Kevin,

Thank you very much for your help, it works! But I have a problem here. If you uses this method "New Sales of $"&TEXT(G11,"0,000.00", if the sales goes below thousand will look very funny. And when both don't have sales in the first 6 months, it will still return as "New Sales of $0,000.00".

Are you able to further help in the formula?

[Kevin UK]

Hi

It returns the values as text, as you require the cell to contain New sales etc. If you can do away with that it will be a lot easier. Maybe have the text part in a column next to the number!

[ec4excel]

Dear Kevin,

Thanks for the reply. I had changed this "New Sales of $"&TEXT(G11,"0,000.00" to "New Sales of $"&TEXT(G11,D11-C11)

and also, return a 0 when no sales.

[ec4excel]

Dear Kevin,

Thanks for the reply. I had changed this "New Sales of $"&TEXT(G11,"0,000.00" to "New Sales of $"&TEXT(G11,D11-C11)

and also, return a 0 when no sales in column C and D.

[Kevin UK]

Hi ec4excel

Thank you for the feed back.

[dogberry]

Hi

Likewise thank you for the feedback.

Chris