1–1011–20

Excel Formula

From: Mod_Inside (MODINSIDE) 9 Dec 2009 15:56
To: ALL1 of 20
36931.1

Hi,

 

Our Company has a load of Filling Stations they supply, and they wanted to use an auto roundup or down in a spreadsheet to change daily fluctuations in pricing to the sites.

 

ie if todays price was 98.25 it should round down to 98.00 if it was 98.67 it would round up to 98.90

 

Anyone any idea how to work that out? I've been reading about Round Max and Ceiling but they dont round after the decimal point or from what my limited knowledge in coding can fathom out.

 

Any ideas anyone?

 

Thanks

Reply
0/0
From: 99% of gargoyles look like (MR_BASTARD) 9 Dec 2009 17:44
To: Mod_Inside (MODINSIDE) 2 of 20
36931.2 In reply to 36931.1

You need to add 0 as the second argument:

 

ROUND(68.25,0)=68
ROUND(68.67,0)=69

Reply
0/0
From: Radio 9 Dec 2009 17:50
To: Mod_Inside (MODINSIDE) 3 of 20
36931.3 In reply to 36931.1

What he means is to add '1' as the second argument (i.e. the number of digits after the decimal point that you want to keep)

 

EDIT: Actually, your first post makes no sense. How can you round from 0.67 to 0.9???

EDITED: 9 Dec 2009 17:52 by RADIO
Reply
0/0
From: 99% of gargoyles look like (MR_BASTARD) 9 Dec 2009 18:37
To: Radio 4 of 20
36931.4 In reply to 36931.3
You're right, to round to 10p you'd use 1 to round to the nearest pound use 0, but the OP doesn't make sense. It was rounding down 25p to the nearest pound that threw me. :-$
EDITED: 9 Dec 2009 18:38 by MR_BASTARD
Reply
0/0
From: ANT_THOMAS 9 Dec 2009 18:44
To: Radio 5 of 20
36931.5 In reply to 36931.3
Because it's the price of petrol I assume. Most petrol prices are to the .9p
Reply
0/0
From: ANT_THOMAS 9 Dec 2009 18:45
To: Radio 6 of 20
36931.6 In reply to 36931.3
But actually, he's said 98.25 should go to 98.00, whereas if it was petrol prices it'd go to 97.9.
Reply
0/0
From: JonCooper 9 Dec 2009 19:01
To: 99% of gargoyles look like (MR_BASTARD) 7 of 20
36931.7 In reply to 36931.4
eh? he's not rounding down to the nearest pound, it's to the nearest penny

ie 98.25p rounded down to 98p

and equally 98.67p rounded up to 98.9p

that second one makes no sense to me, I assume it's some kind of marketing gimmick
Reply
0/0
Message 36931.8 was deleted
From: Mod_Inside (MODINSIDE) 9 Dec 2009 20:22
To: ALL9 of 20
36931.9
Hi,

Our Company has a load of Filling Stations they supply, and they wanted to use an auto roundup or down in a spreadsheet to change daily fluctuations in pricing to the sites.

ie if todays price was 98.25 it should round down to 98.00 if it was 98.67 it would round up to 98.90

Anyone any idea how to work that out? I've been reading about Round Max and Ceiling but they dont round after the decimal point or from what my limited knowledge in coding can fathom out.

Any ideas anyone?

Thanks


Sorry I made an arse of it as I was in a hurry to go out somewhere else.

It should round up or down to the nearest 0.9

If its a low price like 98.25 it should round down to 97.90 or if its above 98.51 then should round up to 98.90

Apologies for the confusion.
Reply
0/0
From: ANT_THOMAS 9 Dec 2009 20:27
To: Mod_Inside (MODINSIDE) 10 of 20
36931.10 In reply to 36931.9

So we're working with .9p.

 

Surely the middle value should be at .4p?

 

94.9<95.4<95.9

 

If <95.4 then it'll be 94.9
If ≥95.4 then it'll be 95.9

Reply
0/0
From: Mod_Inside (MODINSIDE) 9 Dec 2009 20:31
To: ANT_THOMAS 11 of 20
36931.11 In reply to 36931.10

That looks about right.

 

Can I embarass myself and ask for a sample excel sheet ?!?

 

I cant obviously work with the values above but need sheet values to work it out in my head.

 

I know I know but im in no way a programmer and dont understand some of these functions!

 

Many thanks Ant

EDITED: 9 Dec 2009 20:32 by MODINSIDE
Reply
0/0
From: ANT_THOMAS 9 Dec 2009 20:35
To: Mod_Inside (MODINSIDE) 12 of 20
36931.12 In reply to 36931.11

I'd help if I could, but I'm fairly shit with Excel.

 

I know what you want, but I don't exactly know how to implement it. But since we've cleared up what you're looking for then maybe someone else will be able guide you!

Reply
0/0
From: Matt 9 Dec 2009 21:51
To: Mod_Inside (MODINSIDE) 13 of 20
36931.13 In reply to 36931.9

Unless I'm misunderstanding things:

 

=IF(MOD(x,FLOOR(x,1))>=0.51,FLOOR(x,1)+0.9,FLOOR(x,1)-0.1)

 

I'm no good at writing Excel macros, but you can put that directly in the cell you want to update and change x to the cell number, i.e. A1.

EDITED: 9 Dec 2009 21:51 by MATT
Reply
0/0
From: patch 9 Dec 2009 22:55
To: Matt 14 of 20
36931.14 In reply to 36931.13
Wouldn't it be easier to round to the nearest pound/penny/whatever, and then subtract 0.1? Or am I too tired to be thinking about this kind of thing?
Reply
0/0
From: Mod_Inside (MODINSIDE) 9 Dec 2009 23:01
To: ALL15 of 20
36931.15

Thanks all, especially Ant and Matt. I'll give them a go tomorrow and see how I get on.

 

I'll let you know, thanks again though.

Reply
0/0
From: Matt 9 Dec 2009 23:13
To: patch 16 of 20
36931.16 In reply to 36931.14
Yes, but no. Excel's round function rounds up on 0.50 and not 0.51 as Mod Inside wanted.
Reply
0/0
From: Mod_Inside (MODINSIDE) 9 Dec 2009 23:59
To: Matt 17 of 20
36931.17 In reply to 36931.13

Matt your a feckin genius!

 

It works perfect, thank you very much. I would honestly never in a million years worked that out, I've honestly been reading shitloads about Roundup/Ceilings/Floors/etc etc.

 

Didn't have a clue what to do next.

 

Thanks a lot, really appreciated.

 

PS. Attached a test Spreadsheet to show the calculation for the future, if anyone is needing it (unlikely I know)

Attachments:
Reply
0/0
From: Peter (BOUGHTONP) 9 Dec 2009 23:59
To: Matt 18 of 20
36931.18 In reply to 36931.16
Given the reply to Ant's question, I would challenge MI to confirm if he specifically wants 0.5 rounding down or not.

Cus ROUND(x)-0.1 (or whatever) is far nicer than that big multi-storey thing.
Reply
0/0
From: Matt10 Dec 2009 10:44
To: Peter (BOUGHTONP) 19 of 20
36931.19 In reply to 36931.18
Agreed, it is against what we're taught, but these petrol people have funny rules about prices.
Reply
0/0
From: Radio10 Dec 2009 11:45
To: Matt 20 of 20
36931.20 In reply to 36931.19
It made a bit of sense when petrol was 35.9p a litre, but now that its over a pound, sticking to the 0.9p principle is just silly.
Reply
0/0