Converting a value to a currency please help.

Hi guys

New member and a very new Excel user, looking for some help. I think I may be using this forum quite a bit!

Basically my biggest customer has asked me to produce invoices in excel as opposed to Sage because it works better for them, with this in mind I will have to use excel for everything......The problem is I have no idea about excel, well.......Ive just learnt some basic formulas, ive set up a basic template.....but heres my problem.

My invoice layout is basically like this.

Travel Hours Site Hours Rate Additional Material Total

2.00 2.00 1 £180.00
1.00 1.00 1.5 £135.00

I have different rates for different customers......then I have 3 rates for each of those customer. So If I charge £45p/h to one customer (normal rate) it then becomes 67.50p/h (1.5) and £90.00p/h (2).....depending on the time of call out. You will see above that one call out could incorperate two different rates, depending on arrival and departure time etc.

1) How do I get a value (travel and site hours) converted into £
2) How do I set a specific value for each rate customer, and rate? Can I have a rate saved so i can then select that rate relevant to that customer when inputting?

Any help would be greatly appreciated, im sorry to be a boring noob.....!

Originally Posted by Sambo kid

Hi and welcome to the forum.
From what you posted it would seem that you could do what you want with different types of IF formulas but we'd need more details to know how to structure them.
There are likely to be other ways too.
As for the cell that the results are in, you just format that as currency and that should take care of any display issues.

Yeah, Ive been having a play, what im struggling with is how to format it to understand that eg 1.5 rate at £45 is £67.50. Obviously I can do the basic addition so it equates the column total.......just dont understand how to relate x to z and save it for future reference.

I wish Word could do this

Originally Posted by daffodil11

Hello and welcome to the forums.

In the future, I'd highly recommend you draw up an example of your questions into a file and attach it. This will save considerable amount of time on the side of the user from having to manually build tables and information into a fresh file. Attachments can be made by going to the Go Advanced button, and then using the manage attachments on the following page.

You could set rates in a table and then reference those rates with a simple formula. In the attachment, I'm using VLOOKUP to reference a table, by finding the customer's name and pulling back the respective rate from the 2nd column of the table.

Attachment 270293

Thank you, I never even thought of that. Im totally naive to be honest. Ive look at basic guides but they dont really do what I need. So basically I other software for a reference table?

Ill attempt to attach a file but It wont have much on!

Is the rate dependant on individual entries, or is the rate dependant on the customers specifically?

If you design a table with the multipliers, a base rate, and total rate you could refer to it throughout your calculations.

You could then enter a customer's name or ID and formulaically calculate their total price, using VLOOKUP to find them in the rate table. You could even change base rates and have the entire workbook update itself accordingly with just a couple of few keystrokes.

Originally Posted by daffodil11

Is the rate dependant on individual entries, or is the rate dependant on the customers specifically?

If you design a table with the multipliers, a base rate, and total rate you could refer to it throughout your calculations.

You could then enter a customer's name or ID and formulaically calculate their total price, using VLOOKUP to find them in the rate table. You could even change base rates and have the entire workbook update itself accordingly with just a couple of few keystrokes.

Well, Ive only scratched the surface but can see what an immensely useful tool Excel could be to everything I do, but like I say Im very tender at this stage so be gentle!

The rate is dependant on individual entries, so in other words I have 3 rates. One being normal, one being time and a half and one being double....so irrespective of the customer base rate, the multiplication for each is the same so....

Customer A) 45........or multiplied by 1.5 or 2
Customer B) 60........or multiplied by 1.5 or 2

Does that make sense Daffodil?

Apologies Mr Mod.

invoice play.xlsx

This should give you a basic idea. Firstly Im tryng to select tr, site, to add them together for about 20 rows...i only seem to be ableto do one at a time. Then get the total hours converted into the total, dependent on rate. then theres the VAT

Ill have a look at that daffodill, thanks

Thanks doffodil...................very much appreciated, ill have a gander........thanks so much

I'll probably annoy you some more tomorrow

Woo hoo! I've never had anyone annoy me.

Glad I could help.

Originally Posted by FDibbins

oooo you have a stalker now daffodil (just kidding)

I dont know.....typical vbulletin, mods wasting space