Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

DIV/0 error

4 views
Skip to first unread message

helenapereira

unread,
Jan 8, 2010, 5:56:08 AM1/8/10
to
I have a workbook that gives the %age profit of products sold on a month basis. Each month has a separate worksheet to allow for identifcation of variances from expected on a monthly basis.

I want to summarise the monthly data by supplier and have created a worksheet using the SUMIF function to pull the information through from the individual monthly sheets. =SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])

Unfortunately I've hit a bit of a brick wall, we don't sell all the lines every month and I am getting a DIV/0 error.

Is there any way to return these cells as a blank?

Thanks for any help you can give


Submitted via EggHeadCafe - Software Developer Portal of Choice
COM+ Services Overhead in .NET
http://www.eggheadcafe.com/tutorials/aspnet/aab197cf-64da-4233-9819-02edbea0e9fb/com-services-overhead-in.aspx

Bob Phillips

unread,
Jan 8, 2010, 7:18:07 AM1/8/10
to

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales]),"")

---
HTH

Bob Phillips

<Helena Pereira> wrote in message
news:2010185565he...@bespoke-foods.co.uk...

walrus

unread,
Jan 8, 2010, 7:23:01 AM1/8/10
to
Try this

=IF(ISERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])),"",(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])))

"Helena Pereira" wrote:

> .
>

T. Valko

unread,
Jan 8, 2010, 10:38:14 AM1/8/10
to
I never use that structured syntax...

Wouldn't the result of that formula always be 1 (provided it doesn't
generate an error)?

--
Biff
Microsoft Excel MVP


"Bob Phillips" <bob.ph...@somewhere.com> wrote in message
news:OtHLlyFk...@TK2MSFTNGP02.phx.gbl...

helenapereira

unread,
Jan 11, 2010, 7:43:08 AM1/11/10
to
The formula is working.
thansk for the advise

Bob Phillips wrote:

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sa

08-Jan-10

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales]),"")

---
HTH

Bob Phillips

<Helena Pereira> wrote in message

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice

Some Useful Javascript Debugging Functions
http://www.eggheadcafe.com/tutorials/aspnet/3e2c8093-4f26-41c4-83d2-cefc71222c71/some-useful-javascript-de.aspx

helenapereira

unread,
Jan 11, 2010, 7:50:49 AM1/11/10
to
This formula works too, thanks for your help.

walrus wrote:

Try
08-Jan-10

Try this

=IF(ISERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])),"",(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])))

"Helena Pereira" wrote:

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice

SQL Server Query Analyzer Runs Fast - Stored Procedure Runs Slow
http://www.eggheadcafe.com/tutorials/aspnet/353cb16c-3cde-44dd-a264-750c1ce4e423/sql-server-query-analyzer.aspx

0 new messages