Excel formula question Page 1 / 2

john136, Jan 13, 8:36am
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

niklaus, Jan 13, 8:38am

ross1970, Jan 13, 8:39am
"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.

dunedin_ree, Jan 13, 8:45am
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.

john136, Jan 13, 8:45am
Thanks Guys, had been fiddling for ages. Sharon

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

ross1970, Jan 13, 8:54am
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. . . . .

simp1060, Jan 13, 8:55am
0. 875 points, aye

dunedin_ree, Jan 13, 8:59am
How? Isn't GST 12. 5%?

gyrogearloose, Jan 13, 9:00am
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. . .

ross1970, Jan 13, 9:00am
Yes it is. . . . . and?

dunedin_ree, Jan 13, 9:02am
So . 88 recurring does not equal the GST exclusive amount. Yes, it's roughly right, but explain to me how . 875 is NOT correct.

revliskci, Jan 13, 9:05am
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.

ross1970, Jan 13, 9:06am
(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.

niklaus, Jan 13, 9:07am
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.

dunedin_ree, Jan 13, 9:07am

Ross, do you dig?

dunedin_ree, Jan 13, 9:12am
Yes, you've successfully proved that . 875 ! = . 88 recurring.

simp1060, Jan 13, 9:17am

ross1970, Jan 13, 9:19am
ffs Dunedin ree, please tell me your masters isn't in anything even remotely related to mathematics!

dunedin_ree, Jan 13, 9:22am
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.


gyrogearloose, Jan 13, 9:30am
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.

ross1970, Jan 13, 9:35am
Please give a simple equation where a gst incusive amount is multiplied by your 0. 875 to result in the gst exclusive amount.

lostdude, Jan 13, 9:35am
divide by 112. 5 then multiply by 100.

lostdude, Jan 13, 9:37am
You can't, since the exclusive amount had 12. 5% ADDED to it, the result will be wrong.

ross1970, Jan 13, 9:38am
EXACTLY! ! ! try telling dn ree that

Share this thread

Buy me a coffee :)Buy me a coffee :)