RE: formula for balance & over expense amount

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Mark Graesser (anonymous_at_discussions.microsoft.com)
Date: 05/05/04 

Date: Wed, 5 May 2004 06:36:04 -0700

Hi Aboiy,
There are two ways to hide the zeros.

1) You can go to Tools>Options>View and uncheck "Zero values". However, this will hide every zero on the ***.

2) You can put your formula inside an IF statment. The If statement would be set up as:
=IF(yourformula=0,"",yourformula)
This setup can create some long formulas, but it allows you to hide the zeros values you want to, while still displaying other zero values.

So for your sample the formula would be:

=IF(SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2:$C$100))=0,"",SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2:$C$100)))

Glad to hear that I have been helpful.

Good Luck,
Mark Graesser
mark_graesser@yahoo.com
Boston MA

     
     ----- Aboiy wrote: -----
     
     
     Mark,
     
     Everything is clear to me now, but a small favor, can we
     apply the same principle of showing blank if the expenses
     equals to "0" for col. D.
     
     Again thank you for your time and patience.
     
     Thanks and regards.
     
     aboiy
     
     
     
     
>-----Original Message-----
>Hi aboiy,
>I see now that I used the wrong *** names. When I put
     the formula together I had the expenses on *** 1. The
     formula you need would be:
>>=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2:$C$100))
>>You need to use a range because full column reference
     (ex. B:B) won't work in the SUMPRODUCT function.
>>Basically the (Sheet2!$B$2:$B$100=B2) will compare the
     value in *** 2 column B with the value in cell B2 on
     *** 3. If they match it returns a 1, if not it returns
     a zero.
>>The (Sheet2!$C$2:$C$100) returns the expense value.
     These two arrays are then multiplied and the components
     added up.
>>So in your example, with 360 in B2:
>>=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2:$C$100))
>>=(1,0,0,1,0) * (3800, 7500, 1500, 90, 0)
>>=(1*3800) + (0*7500) + (0*1500) + (1*90) + (0*0)
>>=3800 + 0 + 0 + 90 + 0
>>=3890
>>When you copy this formula down it will give you the
     totals for the other accounts.
>>Let me know if you have any trouble.
>>Good Luck,
>Mark Graesser
>mark_graesser@yahoo.com
>Boston MA
>> ----- Aboiy wrote: -----
>>> Good day Mark,
>> I'm in the office now and trying to apply your
     formula,
> is it possible to make reference on *** 2 col."C"
> wherein the expenses can be totalled based on the
     account
> in col."B" the same account can be found in *** 1
     with
> corresponding budget to follow.
>> *** 2 (Daily Expenses)
> col. A B C
> No. Acct. Expenses
> 1- 360 3,800
> 2- 247 7,500
> 3- 247
     1,500
> 4- 360 90
> 5- 050 0
>> Result should look like this:
>> *** 3 (Summary Report)
> col. A B C D E F
> No. Acct. Budget Expenses Balance
     Over Exp.
> 1- 360 4,600 3,890 710
> 2- 280 3,500
> 3- 247 1,200 9,000
     7,800
> 4 - 315 7,900
> 5 - 050 8,400
>> Notice that in *** 2
> 1 - item 1&4 having same acct. "360"
> 2 - item 2&3 having same acct. "247"
>> For your usual cooperation.
>> Thanks and regards.
>> aboiy
>>>-----Original Message-----
>>Hi aboiy,
>>>Enter the following formula in cell D2 on sheet3:
>>>=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
     $C$2:$C$100))
> or
>>=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
> $C$2:$C$100))=0,"",SUMPRODUCT((Sheet1!$B$2:$B$100=B2)
     *
> (Sheet1!$C$2:$C$100)))
>>>This will give you the total expenses on *** 1
     for the
> account number in coulmn B.
>>>For question 2 use =D2-C2 or =IF(D2-C2<=0,"",D2-C2)
>>>For question 3 use =C2-D2 or =IF(C2-D2<=0,"",C2-D2)
>>>In all of the above cases the second formula will
     prevent
> a zero or negative value from showing up.
>>>>Good Luck,
>>Mark Graesser
>>mark_graesser@yahoo.com
>>Boston MA
>>> ----- aboiy wrote: -----
>>> Hi,
>>> I have this spread*** for our daily
     expenses
>>> *** 1 (Budget & Cost Center)
>> col. A B C
>> No. Acct. Budget
>> 1- 360 4,600
>> 2- 280 3,500
>> 3- 247
     1,200
>> 4 - 315 7,900
>> 5 - 050 8,400
>>> *** 2 (Daily Expenses)
>> col. A B C
>> No. Acct. Expenses
>> 1- 360 3,800
>> 2- 247 7,500
>> 3- 247
> 1,500
>> 4- 360 90
>> 5- 050 0
>>> *** 3 (Summary Report)
>> col. A B C D
     E F
>> No. Acct. Budget Expenses Balance
     Over
> Exp.
>> 1- 360 4,600
>> 2- 280 3,500
>> 3- 247
     1,200
>> 4 - 315 7,900
>> 5 - 050 8,400
>>> I need to make a formula in sheet3
>> 1- col.D - totalling the expenses in sheet2
     base
> on the
>> account.
>> 2- col.E - show only the balance "less than"
     the
> budget
>> in col.C
>> 3- col.F - reflect only the "excess amount"
     from
> the
>> budget in col.C
>> 4- show blank " " instead of 0 when no
     expenses
> are
>> incurred for col.D,E,& F.
>>> For your kind assistance.
>>> Thanks and regards.
>>> aboiy
>>>>>>.
>>>.
>

Quantcast