quick EXCEL question

[Testin da Cable]

is there a way to cut a row and paste it as a column?

arrg it's doing my nut in!!

 

[dysfunction]

Past Special transpose.

 

[Testin da Cable]

you rock matey!

 

[FatBusinessman]

...and they all went home for tea and cakes, and lived happily ever after

 

[Embattle]

Originally posted by Testin da Cable
is there a way to cut a row and paste it as a column?

arrg it's doing my nut in!!

Think we better info the Euro police that TDC is actually working....omg.

 

[Testin da Cable]

wellll...makes a change tbh

 

[FatBusinessman]

tbh k asl pls thx ffs

 

[darthshearer]

Originally posted by FatBusinessman
tbh k asl pls thx ffs

?? English

 

[dysfunction]

Bloody hell

 

[mank!]

Originally posted by FatBusinessman
tbh k asl pls thx ffs

omfg noob fu

 

[]SK[]

Theres Excel in linux now? o_0

 

[FatBusinessman]

Originally posted by mank!
omfg noob fu

stfu, rtfm ffs

 

[Tom]

I have a large spreadsheet in excel for doing my monthly books, trouble is, that it doesn't quite add the rows up perfectly.

=PRODUCT(-F8/1.175+F8)

I use that for calculating the vat from the gross

=SUM(G4:G34)

and that for calculating the sum of the vat column. It usually is a few pence out. What am I doing wrong?

 

[Xtro 2.0]

Originally posted by Tom.
What am I doing wrong?

Breathing.










Only joking Tom

 

[Scouse]

Why don't you multiply the sum of the gross by .175?

that's yer VAT m8

 

[Tom]

No it isnt, 0.175 * gross only gives you 17.5% of the gross. 0.175 * net gives you the vat.

What you mean is dividing by 1.175. That gives you the vat from the gross.

 

[Xtro 2.0]

How big are your cocks btw?

/edit : of course I'm talking about your livestock not your penis size.

 

[Lester]

Do a column of gross total (b).multiply each cell by .8510624 (column c)and in the third column (d) take one away from the other. Do "sum" at the bottom of each and that's that. Usually....

 

[MYstIC G]

Originally posted by Tom.
I have a large spreadsheet in excel for doing my monthly books, trouble is, that it doesn't quite add the rows up perfectly.

=PRODUCT(-F8/1.175+F8)

I use that for calculating the vat from the gross

=SUM(G4:G34)

and that for calculating the sum of the vat column. It usually is a few pence out. What am I doing wrong?

Have you made sure all the figures are rounding to 0.00 style before excel totals them?

 

[Sharma]

Originally posted by FatBusinessman
tbh k asl pls thx ffs

Someones been reading the excal daoc boards i think.

 

[FatBusinessman]

DAoCers use "applications"?

 

[Tom]

Originally posted by MYstIC G
Have you made sure all the figures are rounding to 0.00 style before excel totals them?

I'm not sure I understand you, I have all cells with numbers in formatted in accounting style with 2 decimal places?

 

[Tom]

Originally posted by Lester
Do a column of gross total (b).multiply each cell by .8510624 (column c)and in the third column (d) take one away from the other. Do "sum" at the bottom of each and that's that. Usually....

I just tried those sums, unfortunately they aren't 100% accurate, and I'm not sure if excel adds the figures shown in the cells, or the figures that have been calculated prior to any 'rounding off'

Thing is, my sums are perfect, but the sums at the bottom of each column are a few pence out! Arghh!

 

[Sharma]

Originally posted by FatBusinessman
DAoCers use "applications"?

Well, the executable it techincally and application if you look it up

<looks>

target type: Application

<wonders why he still has it installed>

 

[MYstIC G]

Originally posted by Tom.
I'm not sure I understand you, I have all cells with numbers in formatted in accounting style with 2 decimal places?

My understanding of excel is that a format to 2 decimals doesn't actually make the number itself 2 decimals. It just takes 117.546 and only shows 117.57

From my understanding if you've got a calculation that works on these, Excel still uses the full values which may not round in the same way, leading to your few pence losses.

Basically I think you should create a copy of your spreadsheet & use the round forumula where necessary in the calculations instead of excels decimal formatting & see if that helps.

 

[evilmonkeh]

tom make sure you put the sums in brackets, it helps things not go wrong.
ie:
=PRODUCT((-F8/1.175)+F8)
if you just wanted the vat it would be easier to do:
=sum(F8*0.175)


also you can change the rounding off etc by selecting the cell and going to Format > Cells

 

[Testin da Cable]

just bias the thing so it rounds in your favour and forget about it

 

[FatBusinessman]

Originally posted by MYstIC G
Basically I think you should create a copy of your spreadsheet & use the round forumula where necessary in the calculations instead of excels decimal formatting & see if that helps.

More specifically, use this:

=ROUND(PRODUCT(-F8/1.175+F8),2)

Which will round it to the 2 d.p. you require

 

[Tom]

Thx businessman I'll try that.

 

[Tom]

Originally posted by evilmonkeh
tom make sure you put the sums in brackets, it helps things not go wrong.
ie:
=PRODUCT((-F8/1.175)+F8)
if you just wanted the vat it would be easier to do:
=sum(F8*0.175)


also you can change the rounding off etc by selecting the cell and going to Format > Cells

It's worth noting for everyone here who uses VAT, that 17.5% of £117.50 will not give you the correct figure (ie £17.50).