SIMPLE EXCEL SS REQUIRED FOR MORNING!!!

Darujhistan

Darujhistan

Darujhistan

Soldato
Joined
28 Oct 2011
Posts
8,778
Can someone please help???

New job and they've just plonked this on me and I can't do it, I've been trying for hours. I got the formulas to work a bit but as soon as try adding headings and spaces it all ****s up. Also my copy of excel at home does not behave the same as my work copy...:(

It's 4 columns. Here's an example set out.

ITEM NAME COST PRICE MARKUP QUOTE PRICE
EMPTY ROW OF CELLS>>>>>>>>>>>>>>>
WINE £1.00 £0.35 £1.35
BEER £2.00 £0.70 £2.70


Right, the markup needed to be added to cost price is always 35% so 'MARKUP' Column will show £0.35 for ex and £0.70 for ex. The fourth column will show the final price ie £1.35 and £2.70 as shown in table (sorry for the over explanation).

If someone would be kind enough to knock me this spreadsheet up if not for tomorrow AM then any time tomorrow I would be eternally grateful.

Thanks in advance.
 
Looks like someone lied at their interviewww! :D

Most likely the cells the formulas are referencing to are being shifted out of order.

Check if the formulas refer to the cells with absolute ($A$1) references.

Alternatively put the headings and things in and then set up the formulas again.
 
See file below

File contains two sheets

1. The first one allows a fix markup that will be applied to all.
2. The second sheet allows you to set the markup per item.

simply enter the markup as the percentage i.e. 35 for 35% and not 0.35.

Link
 
hyperseven said:
Was there any need for posts 3,4 and 5 :rolleyes:
Click to expand...

Absolutely. Op needs to be made aware how long this task should take. Especially since hes told us that its for a new job. He can take it either way its up to him. Stay on a high horse and not care about his skills or just put his head down and take it as advice or motivation to learn the basics of Excel. :)
 
Ok, so say in row 4 we have all our headings (where it says "product, markup value, final price" etc)

In one of the top cells above that put a cell saying "markup" then put 0.35 in the cell next to it, formatting as percentage shows it as 35% for the look of the thing.

So say the cost price starts in b5, and the markup% cell is b3

In c5 (the start of your markup column) put "=b5*$b$3" (you can do this quickly by doing =, click on b5, *, click on b3, hit f4 to add the dollar signs changing to an absolute reference)

In d5 (the start of total price column) put =SUM(b5:c5) (you can click and drag a box to get the cell reference)

Then highlight the 2 formula cells and drag the bottom right corner down to fill them in to lower down (this is where the absolute cell reference is handy because it wont "move" relative to the data), if you have a lot of items in the list already filled in or you want to feel pro then highlight the cells, hold ctrl and double click on the bottom right corner of the selection. This autofills the data down until theres no values immediatly next to it.


Excel is a very useful bit of software to know how to use well and once you get the hang of it things like this are a 60 second job as others have said. Its important to remember the future for other folk having to work on the sheet (hence why i said to use a cell saying 35% as if you ever want to change the markup in future you just change it there and all the prices update rather than messing around inside the formula itself)
 
I remember being lost with excel once. But there's this little Icon on the toolbar, and when you click on it a little animated character pops up in the corner of the screen... Clippy!

And although it's really annoying in MS Word (telling you how to 'Bold' your headings and such), actually it's no end of help when in Excel.
 
You must log in or register to reply here.
Back
Top Bottom