Round up to 5 and Vlookup not working?

[Dibbley247]

Hi

I'm making my own invoice for my at home phone repair sideline, which I will be turning into a proper business in the coming months.
Invoice V1.0.xlsm

I have a lookup list for part prices which will be on the invoice.
but this is where I need help:

For the labour part. I want it to add either a flat rate of say £10,£20 or £30 Which would be in cell H27 (possibly use a lookup in column O).

BUT if the totals including labour is an odd number. I want it rounded up to the Next £5 so my bill total will always end in £5 or £0

IS there a way to do this?

The list in the attached document is nowhere near complete, as I have to add consumables and loads of other parts to add, but I have a few on there to try and get my head round how I want it done.

When printed, it'll only be the invoice, so I really don't care whats around it all to make it work.


Massive thanks in advance!

[Dibbley247]

Sorry. title is wrong, as I managed to sort the Vlookup.

And just to clarify, I want the labour cost to be the flat £10,£20,£30 PLUS what ever is needed to round up to the next £5


Sorry if there is any confusion

[Richard Buttrey]

Hi,

Are you saying that you want the Labour element to include the rounding number? Otherwise if you just round the total then as far as the customer is concerned it won't add up.

What will determine whether the initial Labour amount is 10, 20 or 30. i.e. what's the rule?

[Richard Buttrey]

OK, I see we crossed in the aether

Assuming the labour amount is in O27 then in H27

Please Login or Register  to view this content.

..sorry late edit. I see you want the nearest 5 multiple. Standby one.

[Dibbley247]

Never used a round up! something new to learn!!!

[Richard Buttrey]

Instead in H27

Please Login or Register  to view this content.

[Dibbley247]

Doesn't seem to add up right? it takes it all away again?

[Richard Buttrey]

Hi,

See attached

[Dibbley247]

Ahhh, So sorry! I was looking Right at the O27, but didn't actually look at that location!!!!!!!! Ideal, I will move it around elsewhere and then let you know

Thank you very much!!!!

[Dibbley247]

Spot on! Works a charm!!!!! That 027 will now be a lookup for set prices per phone once I have them, so all parts will be on a dropdown list.

Again, Thanks very much!