View Full Version : EXCEL question

prefect

04-03-2010, 09:36 AM

Could some one help please with a formula to change a total cost which includes gst to a total with gst excluded.

Could do it in 2 formulas I suppose divide by 9 then subtract from total can it be done in one formula?

Thanks

johcar

04-03-2010, 10:06 AM

11.25 =A1-(A1/9) (result = "10")

(where the value "11.25" is in cell A1)

Sweep

04-03-2010, 10:26 AM

Correct answer.

A1/9 is evaluated first then subtracted from the original A1.

prefect

04-03-2010, 10:33 AM

Thanks johcar worked like a treat.

Doing a spreadsheet for a mate who has a franchise saw him with calculator and paper will take him days. By entering in xcel sheet should take an hour at most

The franchise has a 3month period for every 1000 excl gst you spend with him goes into 4 prizes to go to Gt Barrier reef with top Oz fisherman.

His report on cashbook has gst price and needs to enter invoice numbers which have to be printed and sent as proof to franchise hq.

WalOne

04-03-2010, 10:43 AM

Could some one help please with a formula to change a total cost which includes gst to a total with gst excluded.

Could do it in 2 formulas I suppose divide by 9 then subtract from total can it be done in one formula?

Thanks

Column A is the number you need to change

Column B is the formula, which is =SUM(A1/9)*8

If you enter A1 as 11.25, the result in B1 will be 10.00

If you enter A2 as 112.50, the result in B2 will be 100.00

Note as you drag the formula in B1 down, it automatically changes A1 to A2 etc)

Cheers

johcar

04-03-2010, 12:10 PM

Thanks johcar worked like a treat.

Doing a spreadsheet for a mate who has a franchise saw him with calculator and paper will take him days. By entering in xcel sheet should take an hour at most

The franchise has a 3month period for every 1000 excl gst you spend with him goes into 4 prizes to go to Gt Barrier reef with top Oz fisherman.

His report on cashbook has gst price and needs to enter invoice numbers which have to be printed and sent as proof to franchise hq.

Would be more than happy to accompany a prizewinner in recompense for my suggestion... :D :p

WalOne

04-03-2010, 12:14 PM

Would be more than happy to accompany a prizewinner in recompense for my suggestion... :D :p

I'm all for that. And as mine is the most unwieldy of the replies, naturally I would have first right of refusal. :p

johcar

04-03-2010, 01:11 PM

Naturally!! :D

prefect

04-03-2010, 02:06 PM

11.25 =A1-(A1/9) (result = "10")

(where the value "11.25" is in cell A1)

Just a small question why 11.25 when gestapo is 12.5% remember you are talking to a thicky here.

AvonBill

04-03-2010, 02:32 PM

12.5% of 10 = 1.25

10+1.25 = 11.25

johcar

04-03-2010, 03:20 PM

Thanks AvonBill

Yes, prefect, I just used "11.25" because I knew it would result in a nice round result: "10"

If I had used "10", the result would have been "8.89", or "12.5" the answer would have been a very messy "11.1111111111111111"...

WalOne

04-03-2010, 05:01 PM

Thanks AvonBill

Yes, prefect, I just used "11.25" because I knew it would result in a nice round result: "10"

If I had used "10", the result would have been "8.89", or "12.5" the answer would have been a very messy "11.1111111111111111"...

Prefect, to expand on this, you need to format the results column for 2 decimal places so in Johcar's example, you'd end up with 11.11 PM me if you want any how to tips.

prefect

04-03-2010, 05:32 PM

Thanks heaps guys for replies, yes formatted cells as currency to 2 decimal places.

Its up and running saved me mate hours of calculations. Went back to my truck and hour ago and there are 12 cans of woodies on the front seat. Had told him not to bother mates dont have to pay anything but he didnt take any notice.

johcar

04-03-2010, 06:11 PM

That's funny - thought I just heard one of those Woodies being opened.... :D

TeejayR

04-03-2010, 06:17 PM

11.25 =A1-(A1/9) (result = "10")

(where the value "11.25" is in cell A1)

Why not just divide by 1.125 ?

johcar

04-03-2010, 06:57 PM

Cos that's too easy....

WalOne

04-03-2010, 07:10 PM

Why not just divide by 1.125 ?

There's all sorts of ways of arriving at the same answer ;)

In the meantime, the time to send some Woodies from Whest Auckland to the Centre of the Universe at Ellerslie is only 45 minutes at most. I am awaiting my share in breathless anticip ........................ation. ;)

prefect

04-03-2010, 07:17 PM

There's all sorts of ways of arriving at the same answer ;)

In the meantime, the time to send some Woodies from Whest Auckland to the Centre of the Universe at Ellerslie is only 45 minutes at most. I am awaiting my share in breathless anticip ........................ation. ;)

I have dealt to 2 of them already WAL so I cant drive to Ellerslie!

Sweep

04-03-2010, 07:31 PM

There's all sorts of ways of arriving at the same answer ;)

In the meantime, the time to send some Woodies from Whest Auckland to the Centre of the Universe at Ellerslie is only 45 minutes at most. I am awaiting my share in breathless anticip ........................ation. ;)

But last time I looked the Ellerslie Flower show is in Christchurch.:clap

WalOne

04-03-2010, 08:44 PM

But last time I looked the Ellerslie Flower show is in Christchurch.:clap

Rumour is, next year's Ellerslie Flower Show will be held at Taihape, where they'll place flowers in gumboots and throw them down the main street.

:horrified

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.