PDA

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

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