Hey,

Is anyone good in Excel? I have a spreadsheet I have to make for every file at work and I need it to add costs automatically. Basically in a file I will have 30-40 items and each item has a cost, I have to add a certain markup (its not a percentage, just a flat fee that is the same for all items) and rather than doing each calculation I want to type in the actual and have it add the markup and display the total in the same cell.

What formula can I type into the column to have any amount I type in go through this addition.

Thanks for advising

I don't know if this is the way they teach it in Excel classes, but this is what I do. I have to add percentages and flat markups to various columns of numbers at work.

Let's say you have a column of numbers from B1 through B9. Those are your wholesale numbers. Start another column, lets say D1. In your monologue box (I'm not sure if that is what it is called, but the long rectangle on top of your spreadsheet that allows you to type in your characters) type: =sum(B1+.50) or whatever your markup is and enter.

Then right click the D1 box that should be reflecting what your original plus markup is, click copy, then highlight the rest of the D column cells D2 through D9. Click paste. The markup should carry on to the entire column.

I hope that makes sense.

You can't generally have an entry in a cell change its own value... this causes a circular reference error. It can probably be done with a macro, but that gets ugly quickly if you don't want previously entered values to change each time.

The way Emland describes is the way I usually do what you want to do (=b1+.5 works as well... I personally use Sum() only when using a range of values, but that's an aesthetic choice). I'll add that I also usually have one cell used to define the markup amount, and assign it a Name and have the calculation use it... so the Total column will be of the form =b1+Markup. This will make it so that if the markup changes, you can just change that one cell and the values for Total will all update.

Yeah, I see where your going, but then the column showing the cost before markup would still be on the spreadsheet though, we send this to the client (printed) and if I do this and then delete the cost column, the total is wrong.

I guess there isn't a way to do it like I want, I tried and it says it's a circular reference and it can't do it:

I tried =sum(B1 + 27) in B1 and it says it can't have a formula in a cell that refers back to that cell.

Maybe it can't be done the way I am wanting it to work at all. I'll just add one sheet and copy the ammounts to the invoice spreadsheet.

Got to spread the love, Nosnosna, thanks for that one about the "markup cell." Why are obvious things so obscure until someone points them out?

Put the reference column outside your print area and they never need to know!

Quote:

---

Originally Posted by Lowtax4eva

Yeah, i see where your going, but then the column showing the cost before markup would still be on the spreadsheet though, we send this to the client (printed) and if i do this and then delete the cost column, the total is wrong.

I guess there isnt a way to do it like i want, i tried and it says its a circular reference and it can't do it:

I tried =sum(B1 + 27) in B1 and it says it can't have a formula in a cell that refers back to that cell.

Maybe it can't be done the way i am wanting it to work at all. I'll just add in one sheet and copy the ammounts to the invoice spreadsheet.

---

Ah, I have the answer then!

Use a different sheet in the workbook. Sheet 1 will be the one you print and send to the client, and Sheet 2 will be the calculation sheet.

On Sheet 2, set it up the way described above. Then, on Sheet 1, simply reference the appropriate cells in Sheet 2.

So, for Sheet 2, you'll have two columns:
A - Cost: What you enter
B - Total: =A+Markup

For Sheet 1, you'll have the Total column as:
=Sheet2.B1 (or whichever the first column is)

Or, actually now that I think about it, just do the work on the main sheet, and before printing, highlight the working columns, right click, and select 'hide.' They'll still be there, but they won't show up.

Or just do it as originally explained... then highlight the column that you don't want the client to see, right click, and select HIDE. This will HIDE that column ;) :cool: