Auto Calculating Net Profit and YTD Profit - How?

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

  1. lanna

    lanna Guest

    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
    #1
    1. Advertisements

  2. lanna

    Frank Kabel Guest

    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
    #2
    1. Advertisements

  3. lanna

    lanna Guest

    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
    #3
  4. lanna

    Frank Kabel Guest

    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
    #4
  5. lanna

    lanna Guest

    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
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sarah Anderson
    Replies:
    3
    Views:
    338
    Sarah Anderson
    May 19, 2004
  2. Sarah Anderson
    Replies:
    5
    Views:
    242
    Sarah Anderson
    May 19, 2004
  3. Doria/Warris

    How to create a mechanism to get the YTD?

    Doria/Warris, Jun 12, 2004, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    200
    Doria/Warris
    Jun 13, 2004
  4. Aria

    Search for the YTD Actual column, copy and unlink

    Aria, Oct 27, 2006, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    169
  5. Spawnof

    Calculating Sales price by Entering Gross Profit

    Spawnof, Mar 1, 2007, in forum: Microsoft Excel Programming
    Replies:
    9
    Views:
    1,635
    Vinod Jain
    Mar 9, 2007
Loading...

Share This Page