~Newbie Needs Help

[Charlie]

My boss has asked me if I can figure out how to create a basic
spreadsheet for him using Excel. The basic layout needs to be as follows:

Column 1 Column2 Column3 Column 4 Column5
Column6 Column7
ItemDesc ItemNum Cost Fee SoldFor
Percent Profit


The problem is that he wants the "Cost", "Fee", "SoldFor", "Percent" and
"Profit" columns to automatically place a total at the bottom of the
columns. Additionally, he wants to subtract the Cost and Fee from the
SoldFor and calculate 15% to be automatically inserted into the "percent"
column ( "SoldFor" - "Cost" - "Fee" X 15% ). Then he wants to subtract the
figure generated in the "Percent" column from the "SoldFor" column to be
automatically inserted into the "Profit" column.
Basically, he wants to keep track of several dozen ongoing auction items
and the percentage he is paying the guy listing them and wants a running
total of his actual costs, fees, amount he's paying his listing guy and
total clear profit. He wants to be able to hand this information to his
bookkeeper each month for taxes and be able to add items and figures daily
and have a running total to see how he's doing.
Now for the fun part, I have no earthly idea how to begin and I don't
know anyone competent with Excel. Can anyone here help point me in the right
direction?

Thanks,
Charlie

 

[Frank Kabel]

Hi
as a starting point:
1. Totals:
- if you have a fixed nmber of rows just insert a formula like
=SUM(C2:C20) in the last row (e.g. in cell C21)

- if you want to add several rows I would suggest the following:
-> place the total at the TOP
-> use a formula like
=SUM(C2:C1000)
or
=SUM(OFFSET(C2,0,0,COUNTA(C2:C1000))

2.15% formula. Try in F2:
=(E2-D2-C2)*0.15

or with some checking use
=IF(E2<>"",(E2-D2-C2)*0.15,"")

 

[Charlie]

My boss has asked me if I can figure out how to create a basic
spreadsheet for him using Excel. The basic layout needs to be as follows:

Column 1 Column2 Column3 Column 4 Column5
Column6 Column7
ItemDesc ItemNum Cost Fee SoldFor
Percent Profit


The problem is that he wants the "Cost", "Fee", "SoldFor", "Percent" and
"Profit" columns to automatically place a total at the bottom of the
columns. Additionally, he wants to subtract the Cost and Fee from the
SoldFor and calculate 15% to be automatically inserted into the "percent"
column ( "SoldFor" - "Cost" - "Fee" X 15% ). Then he wants to subtract the
figure generated in the "Percent" column from the "SoldFor" column to be
automatically inserted into the "Profit" column.
Basically, he wants to keep track of several dozen ongoing auction items
and the percentage he is paying the guy listing them and wants a running
total of his actual costs, fees, amount he's paying his listing guy and
total clear profit. He wants to be able to hand this information to his
bookkeeper each month for taxes and be able to add items and figures daily
and have a running total to see how he's doing.
Now for the fun part, I have no earthly idea how to begin and I don't
know anyone competent with Excel. Can anyone here help point me in the right
direction?

Thanks,
Charlie

Sorry to follow up on my own post, but we're using Excel 2000. I'm trying to
put it all together on an XP box, but the store network is running Win2000.

Thanks,
Charlie

 

[Gord Dibben]

desc num cost fee sold percent profit
qwerty 1 100 10 125 =E2-(C2+D2)*0.15 =E2-F2

Total for column C =SUM(C2:C10) copy this across columns D,E,F,G

Gord Dibben Excel MVP

 

[Charlie]

You folks are great! Thanks for all of your help. I've used other newsgroups
in the past and I was really expecting a "hire someone to do it" type of
answer. I never figured I'd get the answer in the first half hour.

Thanks,
Charlie