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
x1

="cell"/9*8

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
x1

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.

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.

Explain.

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

Since the public registrations are closed, you must have an invite from a current member to be able to register and post in this thread.
Have an account? Login here.