Ask Experts Questions for FREE Help !
Ask
    Lowtax4eva's Avatar
    Lowtax4eva Posts: 2,467, Reputation: 190
    Ultra Member
     
    #1

    May 3, 2007, 12:21 PM
    Automatic adition in Excel
    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
    Emland's Avatar
    Emland Posts: 2,468, Reputation: 496
    Ultra Member
     
    #2

    May 3, 2007, 12:47 PM
    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.
    Nosnosna's Avatar
    Nosnosna Posts: 434, Reputation: 103
    Full Member
     
    #3

    May 3, 2007, 12:54 PM
    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.
    Lowtax4eva's Avatar
    Lowtax4eva Posts: 2,467, Reputation: 190
    Ultra Member
     
    #4

    May 3, 2007, 12:55 PM
    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.
    Emland's Avatar
    Emland Posts: 2,468, Reputation: 496
    Ultra Member
     
    #5

    May 3, 2007, 12:57 PM
    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?
    Emland's Avatar
    Emland Posts: 2,468, Reputation: 496
    Ultra Member
     
    #6

    May 3, 2007, 12:58 PM
    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.
    Nosnosna's Avatar
    Nosnosna Posts: 434, Reputation: 103
    Full Member
     
    #7

    May 3, 2007, 01:00 PM
    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.
    DrJ's Avatar
    DrJ Posts: 1,328, Reputation: 339
    Ultra Member
     
    #8

    May 3, 2007, 01:05 PM
    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:

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Automatic to Manual [ 2 Answers ]

Is it possiable to change my automatic 93 honda civic, which is automatic to a manual? And if so is it really hard to do so?

Automatic typing [ 13 Answers ]

When I am typing a message, something/someone types in on my message. It's like a ghost typing! How can I get this to stop. Thanks

Automatic dial up [ 2 Answers ]

I have W2000 56modem. I want my computer to automatically dialup so I can go straight to my programs. I know that you have to uncheck the never dial a connection, but mine will not clear or uncheck. What do I do.


View more questions Search