Excel formula question

Hi there. I am setting up a GST spreadsheet. I know that if I have $30 incl - on a calculator you go x 8 then divide by 9 to get the GST exclusive amount of $26. 67 but how do I do this as an excel formula. Have had a look at the tutorials online but they are not of much help. Any help appreciated

geek_john136, Jan 13, 9:36 pm

="cell"/9*8

geek_niklaus, Jan 13, 9:38 pm

"you go x 8 then divide by 9 to get the GST exclusive amount" . . ?

You'd find it easier as a concept if you /9 and then x8.

geek_ross1970, Jan 13, 9:39 pm

what about multiplying it by 0. 875?

That whole divide by 9 and multiply by 8 is an alternative method (which you can do in your head) that you don't need to (shouldn't actually) replicate in a spreadsheet. GST is 12. 5% therefore GST exclusive is 0. 875 of the total amount.

Divide by 8 and multiply by 8 isn't exactly accurate either.

geek_dunedin_ree, Jan 13, 9:45 pm

Thanks Guys, had been fiddling for ages. Sharon

geek_john136, Jan 13, 9:45 pm

If A1 = $30. 00 then my answer is:
=ROUND((A1/9)*8, 2)

geek_gyrogearloose, Jan 13, 9:45 pm

The number you where really looking for to multiply a gst inclusive amount by to get the gst exclusive amount is 0. 88 recurring. . . . but we'll give you points for trying. . . . .

geek_ross1970, Jan 13, 9:54 pm


0. 875 points, aye

geek_simp1060, Jan 13, 9:55 pm


How? Isn't GST 12. 5%?

geek_dunedin_ree, Jan 13, 9:59 pm

My assertion is that you need to use the ROUND(x, 2) function, otherwise your answer is wrong. Especially if you add a long list of un-rounded numbers, the total will be out. . .

geek_gyrogearloose, Jan 13, 10:00 pm

Yes it is. . . . . and?

geek_ross1970, Jan 13, 10:00 pm


So . 88 recurring does not equal the GST exclusive amount. Yes, it's roughly right, but explain to me how . 875 is NOT correct.

geek_dunedin_ree, Jan 13, 10:02 pm

If you divide a figure (say 1) by 9 and then multiply that by 8, you get 0. 8888 etc, but GST is 12. 5% true and that ÷9x8 formula is just a shortcut used on GST forms to make things simpler.

geek_revliskci, Jan 13, 10:05 pm

(1/8*8)=0. 888888`

Example: $100 add gst you get $112. 50

Multiply $112. 50 to get back to the $100. . . . you multiply by 0. 88888`

Multiply $112. 50 by 0. 875 and you get $98. 44, not even close.

geek_ross1970, Jan 13, 10:06 pm

Why does it need to be rounded? The GST inclusive amount is rounded, they are trying to work out the pre GST amount, which has not need to be rounded for any reason that I can think of, and if you round it would be inaccurate.

geek_niklaus, Jan 13, 10:07 pm


+1

Ross, do you dig?

geek_dunedin_ree, Jan 13, 10:07 pm

Yes, you've successfully proved that . 875 ! = . 88 recurring.

geek_dunedin_ree, Jan 13, 10:12 pm

f
a
i
l

geek_simp1060, Jan 13, 10:17 pm

ffs Dunedin ree, please tell me your masters isn't in anything even remotely related to mathematics!

geek_ross1970, Jan 13, 10:19 pm


Seriously, explain it to me. I'm asking.

You went like this:

a x b = c

c / b = a

c / d ! =a

. 88 rec is not the same as . 875.

Explain.

geek_dunedin_ree, Jan 13, 10:22 pm

No. The $30. 00 is NOT rounded, it's simply in units of 1 cent. Therefore the GST exclusive amount, and the GST amount, should also be expressed in units of 1 cent (I challenge you to produce any GST invoice that states the GST amount in anything other than multiples of 1 cent).

Using the Excel display option to truncate the display to 2 decimal places is incorrect - if you add a long list of such numbers, you will get a different answer than the sum of the rounded numbers.

geek_gyrogearloose, Jan 13, 10:30 pm

Please give a simple equation where a gst incusive amount is multiplied by your 0. 875 to result in the gst exclusive amount.

geek_ross1970, Jan 13, 10:35 pm

divide by 112. 5 then multiply by 100.

geek_lostdude, Jan 13, 10:35 pm

You can't, since the exclusive amount had 12. 5% ADDED to it, the result will be wrong.

geek_lostdude, Jan 13, 10:37 pm

EXACTLY! ! ! try telling dn ree that

geek_ross1970, Jan 13, 10:38 pm


Which is exactly what I was asking Ross to explain to me. I was wrong, I was merely asking someone to point out how. Thanks lostdude.

geek_dunedin_ree, Jan 13, 10:50 pm

np ;)

geek_lostdude, Jan 13, 10:53 pm

I did explain it. Post #14. No thanks necessary tho. . . .

geek_ross1970, Jan 13, 10:54 pm


And none given.

geek_dunedin_ree, Jan 13, 10:57 pm

Go on, you know you want to thank me. . . . you just learnt heaps.

geek_ross1970, Jan 13, 11:03 pm

You guys are confusing as hell. . .

to add GST; R=W*(1+0. 125)
where R = retail price; W=wholesale (ex. GST) price, and (1+0. 125) represents "retail price + 12. 5%" (I think you can even type it as "12. 5%" and excel will convert it for you)

to convert back; W=R/(1+0. 125)

Or in excel; put wholesale in A1,
retail price (in B1) will be =A1*(1+0. 125)

Back to wholesale (in C1) will be =B1/(1+0. 125)

geek_little_egypt, Jan 13, 11:37 pm

I will thank you ross1970, this was racking my head in for a bit. . post #14 set me straight.
i just learnt heaps.

geek_tessal, Jan 14, 10:12 am

It's even simpler than #14. exclusive price *9/8 gives inclusive price; inclusive price *8/9 gives exclusive price. $100 *9/8 = $112. 50; $112. 50 *8/9 = $100
No need for any extra complication.

geek_cjdnzl, Jan 14, 12:57 pm

LMFAO @ this thread - you all sound like a bunch of nerds hahaha

geek_nivarah, Jan 14, 1:01 pm

"0. 888 will have rounding errors"

quite right cjdnzl.

The reason I used 0. 8888` was to show ree how her 0. 875 theory was wrong.

ree's mistake in a nutshell: When you add an 1/8th of something to something as you do when adding gst, once you've added it, it no longer is an "1/8th" of the total but has become the 9th part, 1/9th of the total. So to get back to where you started, ie subtract gst you have to remove a 1/9th of the total. (*0. 8888`) or (/1. 125) or a million other ways you want to write it. Ree saying use *0. 875 worked on the theory you could just take an 1/8th part back off, ie * by 7/8 or 0. 875, missing the point that inclusive gst is the 9th part of the total. That ( 0. 875 ) would only work if gst was 1/7 (100/7)% which when added then become the 8th part, so to remove an 8th part * by 7/8 ( 0. 875 ).

geek_ross1970, Jan 14, 1:26 pm


Not all in this thread are nerds, but you might be, with that blanket statement.

geek_cjdnzl, Jan 14, 1:44 pm

Too much geek for me! When I have an Excel problem I go to www.techonthenet.com/excel/formulas/index. php, works every time and a hell of a lot easier to follow than some of the previous threads.

geek_sapper1, Jan 17, 7:16 pm

Add GST multiply amount by 1. 125. to deduct GST divide amount by 9 and multiply by 8. Try doing some sums in Excel to check results.

geek_eurika, Jan 18, 9:03 am

wtf "divide by 9 and multiply by 8? "

To add GST multiply by 1. 125
To remove it, divide by 1. 125

geek_little_egypt, Jan 18, 9:11 am

You guys can be really embarrassing at times.

This is why we can never have company over from the other message boards.

geek_cybertao, Jan 18, 9:16 am

It's exactly the same phqn thing so what does it matter which you use?

*1. 125 = *(9/8)
/1. 125 = *(8/9)

geek_ross1970, Jan 18, 10:45 am

i laughed aloud

geek_0800xford, Jan 18, 11:00 am

John 136
If A1 has $30. 00 in and you want the GST exc in B1, then use this- put the curser in B1 and enter this very simple formula =a1/9*8
Make sure you adjust the decimal point to 2 - you should see the result as 26. 67
Hope this works

geek_loey, Jan 18, 8:43 pm

Yeah how you just explained it above is good, but when you flip between the two with no explanation (like 90% of this thread) it's confusing as phq. Stick to one or the other.

geek_little_egypt, Jan 18, 8:52 pm

not bad

geek_pokemon12, Aug 8, 1:16 pm

the excel function is not bad at all it tell me most of the things i need to know that is why is the most useful for me.

geek_pokemon12, Aug 8, 1:18 pm