Excel spreadsheet needed

[theeohiostate]

I'm not familiar with how to generate a spreadsheet , so I'm hoping to get someone's help in doing so.

 

 

I will trade off your time for doing this by giving you the same time in MFL site customization , coding or graphics.

 

 

Column A - Item description

Column B - Item price

Column C - Item quantity

Coumn D- 7% sales tax (auto generate from item price)

Column E- Total cost (auto generate the total of item + quantity+ sales tax)

 

 

 

Appreciate the help.

[Grits and Shins]

column D ==> =ROUND((B2*C2*0.07),2)

column E ==> =SUM((B2*C2)+D2)

 

Assuming you want the tax amount rounded

 

Copy the formulas into each applicable row ... the example above is for row 2

 

[EDIT]

Corrected column designations.

Edited May 20, 2008 by Grits and Shins

[tonorator]

mike, dude, have you ever launched excel? you just launch it, type in your column headers and enter your data. to do the sales tax and then the totaling are the most basic of excel formulas. you should do it a little differently, however, to have

 

item description A

item price B

item quantity C

subtotal D

tax E

total F

 

your tax is based off the subtotal, not the individual item price.

 

so if your first row contains the headers, your second row is your first row of data.

 

in A2, enter description

in B2, enter price

in C2, enter quantity

in D2, put your first formula, =B2*C2

in E2, calculate the tax, =D2*.07

in F2, calculate the total, =D2+E2

 

very basic. the time needed would be about the time to type in a few of the team names on a MFL site.

 

eta: am i on candid camera?

Edited May 20, 2008 by tonorator

[tonorator]

let me know if you want and i'll put it together and e-mail it to you. just PM me your e-mail addy.

[theeohiostate]

thanks fellas, I'll give it a try.

 

So easy, even a dummy can do it

[westvirginia]

thanks fellas, I'll give it a try.

 

So easy, even a dummy can do it

 

Yeah, but they told me they weren't sure about you.

[Big Country]

Very easy to do, and I can do it for you if you wish.. just PM me.

 

One other things (not trying to complicate) that you may want to consider:

 

Is the sales tax always static? ie, over time, it is possible for the sales tax to change. It may behoove you to have a separate column (that you can hide if you wish) for sales tax percentage, then you tax amount is just a function of multiplying that column by your sum of quantityxitem price. A little cleaner if there are ever adjustments to the sales tax amount than to have the amount imbedded into the formula.

 

If I knew more about the specific use of the spreadsheet, and if you plan to use it for any type of reporting, etc. (this is what I get paid to do at work) I could get more specific with ideas on setting it up that would more easily facilitate these types of uses.

[Grits and Shins]

Very easy to do, and I can do it for you if you wish.. just PM me.

 

One other things (not trying to complicate) that you may want to consider:

 

Is the sales tax always static? ie, over time, it is possible for the sales tax to change. It may behoove you to have a separate column (that you can hide if you wish) for sales tax percentage, then you tax amount is just a function of multiplying that column by your sum of quantityxitem price. A little cleaner if there are ever adjustments to the sales tax amount than to have the amount imbedded into the formula.

 

If I knew more about the specific use of the spreadsheet, and if you plan to use it for any type of reporting, etc. (this is what I get paid to do at work) I could get more specific with ideas on setting it up that would more easily facilitate these types of uses.

 

If it were me and I wanted to make the sales tax a variable I'd do it one of two ways:

 

1) Add a variable (INSERT > NAME) that was the sales amount (in this case .07) and I'd use the variable in the formula

2) Have the Interest rate in the a header and refer to that interest in the formula using the $ so that the same cell is always referenced by the formula

[Big Country]

2) Have the Interest rate in the a header and refer to that interest in the formula using the $ so that the same cell is always referenced by the formula

 

I considered this and it was my first inclination, however, assuming this is some sort of transaction log, the sales tax is what it is at the time of the transaction. By having a single cell reference, it would adjust all transactions regardless of time frame once the reference cell was updated for any adjustment to the tax rate, that is why I went with what would normally be my secondary option of using a separate column for the tax rate.

 

Not positive, but I believe the same would be true with option 1, though I am less familiar using that method.

[Grits and Shins]

I considered this and it was my first inclination, however, assuming this is some sort of transaction log, the sales tax is what it is at the time of the transaction. By having a single cell reference, it would adjust all transactions regardless of time frame once the reference cell was updated for any adjustment to the tax rate, that is why I went with what would normally be my secondary option of using a separate column for the tax rate.

 

Not positive, but I believe the same would be true with option 1, though I am less familiar using that method.

 

You could set up several variables and use the appropriate one in your formula ... of course that would mean that your formula wouldn't necessarily be the same across the entire column. However, if the spreadsheet was set up by taxing authorities then you could have the same formula for each taxing authority using the appropriate variable.

 

So I could set up DALLAS_07TAX as .0825 and FW_07TAX as .0725

 

Sales in Dallas county would use the DALLAS_07TAX variable

Sales in Fort Worth county would use the FW_07TAX

 

 

DALLAS COUNTY

column D ==> =ROUND((B2*C2*DALLAS_07TAX),2)

column E ==> =SUM((B2*C2)+D2)

 

FORT WORTH COUNTY

column D ==> =ROUND((B2*C2*FW_07TAX),2)

column E ==> =SUM((B2*C2)+D2)

 

Then if you needed to change the rate across all sales you simply need to edit the variable and the new rate will be applied across all the existing formulas. If the rate changed and you wanted to maintain the previous tax rates you could set up new rates as DALLAS_08TAX and FW_08TAX and create new formulas for the new sales going forward.

Edited May 20, 2008 by Grits and Shins

[theeohiostate]

tonorator was kind enough to whip it up for me............perfectly.

 

Thanks

[Big Country]

Grits - good points, and that is why in my original post I mentioned that knowing more about the use of the spreadsheet and it's purpose would help better facilitate the best creation of the sheet to meet those needs

[piratesownninjas]

mike, dude, have you ever launched excel?

TOS and Zooty hate/fear technology...