# Auto Calculating Net Profit and YTD Profit - How?

lanna
Guest
Posts: n/a

 25th Mar 2004
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
Guest
Posts: n/a

 25th Mar 2004
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

lanna
Guest
Posts: n/a

 25th Mar 2004
On Thu, 25 Mar 2004 12:14:11 +0100, "Frank Kabel"
<(E-Mail Removed)> 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
Guest
Posts: n/a

 25th Mar 2004
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"
> <(E-Mail Removed)> 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
Guest
Posts: n/a

 27th Mar 2004
On Thu, 25 Mar 2004 18:07:39 +0100, "Frank Kabel"
<(E-Mail Removed)> 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!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Sarah Anderson Microsoft Excel Misc 4 19th May 2004 12:33 PM Sarah Anderson Microsoft Excel Programming 3 19th May 2004 12:23 AM Sarah Anderson Microsoft Excel Discussion 3 19th May 2004 12:23 AM lanna Microsoft Excel Discussion 4 27th Mar 2004 02:20 AM lanna Microsoft Excel Worksheet Functions 5 27th Mar 2004 02:20 AM

Features