# Auto Calculating Net Profit and YTD Profit - How?

Discussion in 'Microsoft Excel Programming' started by lanna, Mar 25, 2004.

1. ### lannaGuest

Hi,

I need to make a spreadsheet that auto calculates the Net Profit and
YTD Profit in Excel 2002.

Here's how the chart is supposed to look like below.

I will be entering data into the columns: (Customer) Name, Date, Gross
(Profit), and Expenses manually.

I want it to generate data in the columns: Net (Profit) and YTD
automatically.

A B C D E F
1 Name Date Gross Expenses Net YTD
2 Ko, K 1/1/04 200 30 170 170
3 Bo, A 1/4/04 100 20 80 250

How is the most efficient way to do this? Can I use a formula for the
E (Net) and F (YTD) columns or do I have to use VB Macros?

I have no real experience with Excel but I'm willing and capable of
learning. My workplace has no Excel manual and my manager gave me this
task and I agreed thinking it was easy but I'm stuck currently
manually entering the data and making calculations with a calculator.

Please post suggestions in newsgroup (not e-mail as my spam filter
filters out too much).

Thanks,
Lanna

lanna, Mar 25, 2004

2. ### Frank KabelGuest

Hi
try the following:
in E2 enter
=IF(C2<>"",C2-D2,"")
and copy this formula down

in F2 enter
=IF(E2<>"",E2,"")
in F3 enter
=IF(E3<>"",F2+E3,"")
and copy this formula down

--
Regards
Frank Kabel
Frankfurt, Germany

lanna wrote:
> Hi,
>
> I need to make a spreadsheet that auto calculates the Net Profit and
> YTD Profit in Excel 2002.
>
> Here's how the chart is supposed to look like below.
>
> I will be entering data into the columns: (Customer) Name, Date,

Gross
> (Profit), and Expenses manually.
>
> I want it to generate data in the columns: Net (Profit) and YTD
> automatically.
>
> A B C D E F
> 1 Name Date Gross Expenses Net YTD
> 2 Ko, K 1/1/04 200 30 170 170
> 3 Bo, A 1/4/04 100 20 80 250
>
> How is the most efficient way to do this? Can I use a formula for the
> E (Net) and F (YTD) columns or do I have to use VB Macros?
>
> I have no real experience with Excel but I'm willing and capable of
> learning. My workplace has no Excel manual and my manager gave me

this
> task and I agreed thinking it was easy but I'm stuck currently
> manually entering the data and making calculations with a calculator.
>
>
> Please post suggestions in newsgroup (not e-mail as my spam filter
> filters out too much).
>
> Thanks,
> Lanna

Frank Kabel, Mar 25, 2004

3. ### lannaGuest

On Thu, 25 Mar 2004 12:14:11 +0100, "Frank Kabel"
<> wrote:

>Hi
>try the following:
>in E2 enter
>=IF(C2<>"",C2-D2,"")
>and copy this formula down
>
>in F2 enter
>=IF(E2<>"",E2,"")
>in F3 enter
>=IF(E3<>"",F2+E3,"")
>and copy this formula down

My Spreadsheet has currently has 30-40 rows (I just posted 2 rows) and
grows everyday by a few lines.

Do I need to paste different formulas in every E and F cell? Or will
Excel fill in the rest of the E and F cells with formulas?

lanna, Mar 25, 2004
4. ### Frank KabelGuest

Hi
just copy these formulas or drag them down. The row idex will change
automatically

--
Regards
Frank Kabel
Frankfurt, Germany

lanna wrote:
> On Thu, 25 Mar 2004 12:14:11 +0100, "Frank Kabel"
> <> wrote:
>
>> Hi
>> try the following:
>> in E2 enter
>> =IF(C2<>"",C2-D2,"")
>> and copy this formula down
>>
>> in F2 enter
>> =IF(E2<>"",E2,"")
>> in F3 enter
>> =IF(E3<>"",F2+E3,"")
>> and copy this formula down

>
> My Spreadsheet has currently has 30-40 rows (I just posted 2 rows)

and
> grows everyday by a few lines.
>
> Do I need to paste different formulas in every E and F cell? Or will
> Excel fill in the rest of the E and F cells with formulas?

Frank Kabel, Mar 25, 2004
5. ### lannaGuest

On Thu, 25 Mar 2004 18:07:39 +0100, "Frank Kabel"
<> wrote:

>Hi
>just copy these formulas or drag them down. The row idex will change
>automatically

Thanks so much Frank! It works great!

How did you learn such formulas? I would have never thought up that
formula from looking at the Excel Help File. Are you just a genius?
Do you have any Excel books to recommend?

Thanks again! You saved my job!

lanna, Mar 27, 2004