PC Review


Reply
Thread Tools Rate Thread

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
 
Reply With Quote
 
 
 
 
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


 
Reply With Quote
 
 
 
 
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?
 
Reply With Quote
 
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?


 
Reply With Quote
 
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!
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Total YTD Income Per Client and YTD Commission Paid Out From Adjacent Columns? Sarah Anderson Microsoft Excel Misc 4 19th May 2004 12:33 PM
How to Total YTD Income Per Client and YTD Commission Paid Out From Adjacent Columns? Sarah Anderson Microsoft Excel Programming 3 19th May 2004 12:23 AM
How to Total YTD Income Per Client and YTD Commission Paid Out From Adjacent Columns? Sarah Anderson Microsoft Excel Discussion 3 19th May 2004 12:23 AM
Auto Calculating Net Profit and YTD Profit - How? lanna Microsoft Excel Discussion 4 27th Mar 2004 02:20 AM
Auto Calculating Net Profit and YTD Profit - How? lanna Microsoft Excel Worksheet Functions 5 27th Mar 2004 02:20 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:01 PM.