Customer Billing Formula

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Customer Billing Formula

Amichai Rotman
Hey All,

I have a bunch of customers I give phone support to.

I'd like to create a spreadsheet to calculate how much I should charge them
each month. Each customer has a different hourly rate.

So, here is what I want to do:

Sheet per customer
For each customer I enter the following:

Column A: Date
Column B: Time (in minutes - HH:MM, 24hrs format)
Column H: A formula to multiply the contents of B2:B32 times hourly rate....

Obviously I need help with that formula...

Thanks!

Amichai.
Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

Dan Lewis
On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:

> Hey All,
>
> I have a bunch of customers I give phone support to.
>
> I'd like to create a spreadsheet to calculate how much I should charge them
> each month. Each customer has a different hourly rate.
>
> So, here is what I want to do:
>
> Sheet per customer
> For each customer I enter the following:
>
> Column A: Date
> Column B: Time (in minutes - HH:MM, 24hrs format)
> Column H: A formula to multiply the contents of B2:B32 times hourly rate....
>
> Obviously I need help with that formula...
>
> Thanks!
>
> Amichai.

     Have you set up a spreadsheet the way you stated it above? Is
Column A formatted for dates? Is Column B formatted for time using the
HH:MM format?
     Format for entering a formula in a cell:
1. Always begin the formula with an equal sign =.
2. Enter A2 after the equal sign.
3. Enter an asterisk after the A2.
4. Enter the hourly rate R followed by the Enter key.
     Do this in cell H2. Then use the up key to move the outlined
rectangle back to H2. The formula in the window should be
=A2*R. (Yes, it is this simple.)
     Entering a formula into adjacent cells:
1. Place the outlined rectangle in the first cell.
2. Move the cursor over the small square at the bottom right corner
(vortex) or the outlined rectangle. The cursor becomes a small plus sign
+.
3. Drag and drop the plus sign + to the last cell.
     This process can be used to copy a formula in a vertical or
horizontal range of cells.
     Begin at cell H2 and drag down to H32. Click any of these cells to
see the formula entered into that cell.
     Another suggestion: Do this for one sheet. Also enter the Headings
in cells A1, B1, and H1. Now highlight a block of cells containing
columns A through H and rows 1 through 32. A1 should be at the top left
of this block of cells, and H32 should be at the bottom right of it.
Copy this. In each of the sheets, paste this block of cells into cell
A1. You have now formated the sheets and entered the formulas in column
H.
     Now to modify the formulas of each sheet: click cell H2. The
formula appears in the window above the column headers. Change the rate
to what you want in that sheet and click the green check mark to the
left of the window. (It only appears after you change something in this
window.) Now drag and drop the formula down to cell H32. Do this for all
the cells. You should be good to go.

Dan


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

openoffice.mbourne
Dan Lewis - [hidden email] wrote:

> On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:
>> Hey All,
>>
>> I have a bunch of customers I give phone support to.
>>
>> I'd like to create a spreadsheet to calculate how much I should charge them
>> each month. Each customer has a different hourly rate.
>>
>> So, here is what I want to do:
>>
>> Sheet per customer
>> For each customer I enter the following:
>>
>> Column A: Date
>> Column B: Time (in minutes - HH:MM, 24hrs format)
>> Column H: A formula to multiply the contents of B2:B32 times hourly rate....
>>
>> Obviously I need help with that formula...
>>
>> Thanks!
>>
>> Amichai.
>
>       Have you set up a spreadsheet the way you stated it above? Is
> Column A formatted for dates? Is Column B formatted for time using the
> HH:MM format?
>       Format for entering a formula in a cell:
> 1. Always begin the formula with an equal sign =.
> 2. Enter A2 after the equal sign.
> 3. Enter an asterisk after the A2.
> 4. Enter the hourly rate R followed by the Enter key.
>       Do this in cell H2. Then use the up key to move the outlined
> rectangle back to H2. The formula in the window should be
> =A2*R. (Yes, it is this simple.)

Not quite that simple, unfortunately. If the cells are formatted as
HH:MM (as Amichai says they are), the numeric value of the cell is the
fraction of a day that time represents, not the number of minutes. So
what appears as 00:30 (30 minutes) is actually 0.02083333...
(=30/(24*60)) as far as any formula is concerned.

=A2*60*24*(Rate_per_minute)
should do it - A2*60*24 converts the time into a number of minutes, then
multiplied by your minutely rate. I'm not sure how reliable that would
be if A2 is greater than 24 hours, but that probably isn't an issue to
you (unless you get a lot of very long calls!)

You may want to put the minutely rate in another cell, say J1, and use
the following in H2:
=A2*60*24*$J$1
Then you can easily change the rate for each customer. Using $J$2
(instead of just J1) ensures that J1 is used in every line when you copy
the formula to other cells (as Dan describes below).

>       Entering a formula into adjacent cells:
> 1. Place the outlined rectangle in the first cell.
> 2. Move the cursor over the small square at the bottom right corner
> (vortex) or the outlined rectangle. The cursor becomes a small plus sign
> +.
> 3. Drag and drop the plus sign + to the last cell.
>       This process can be used to copy a formula in a vertical or
> horizontal range of cells.
>       Begin at cell H2 and drag down to H32. Click any of these cells to
> see the formula entered into that cell.
>       Another suggestion: Do this for one sheet. Also enter the Headings
> in cells A1, B1, and H1. Now highlight a block of cells containing
> columns A through H and rows 1 through 32. A1 should be at the top left
> of this block of cells, and H32 should be at the bottom right of it.
> Copy this. In each of the sheets, paste this block of cells into cell
> A1. You have now formated the sheets and entered the formulas in column
> H.
>       Now to modify the formulas of each sheet: click cell H2. The
> formula appears in the window above the column headers. Change the rate
> to what you want in that sheet and click the green check mark to the
> left of the window. (It only appears after you change something in this
> window.) Now drag and drop the formula down to cell H32. Do this for all
> the cells. You should be good to go.
>
> Dan
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

Amichai Rotman
Thanks guys,

But - none of it worked...

I'll describe my needs again(disregard the last one):

I have a customer with an hourly rate of 150. Here is what I entered in the
sheet:

A1: Date (Header) - Column A formated as Date (DD-MM-YYYY)
B1: Time (Header) - Column B formated as Time (HH:MM)
G1: Subtotal - how much to charge this month (Header)

A2: The first line with data - the date of the first call this month
B2: The duration of the call - 00:45 (forty five minutes)

What I want to achieve:

H1 should show me how much I have earned from this customer till now, so I
need it to multiply 150 by the time I spent for each line (B2:B32)*150 - or
something like that...

Until now I done this with a pocket calculator, for example:

0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of  188.50

I just want H1 to reflect that sum if I calculate 150 * (B2:B32)

I hope it is now clearer...

Thanks!

Amichai.


On Thu, Jan 13, 2011 at 21:33, <[hidden email]> wrote:

> Dan Lewis - [hidden email] wrote:
>
>> On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:
>>
>>> Hey All,
>>>
>>> I have a bunch of customers I give phone support to.
>>>
>>> I'd like to create a spreadsheet to calculate how much I should charge
>>> them
>>> each month. Each customer has a different hourly rate.
>>>
>>> So, here is what I want to do:
>>>
>>> Sheet per customer
>>> For each customer I enter the following:
>>>
>>> Column A: Date
>>> Column B: Time (in minutes - HH:MM, 24hrs format)
>>> Column H: A formula to multiply the contents of B2:B32 times hourly
>>> rate....
>>>
>>> Obviously I need help with that formula...
>>>
>>> Thanks!
>>>
>>> Amichai.
>>>
>>
>>      Have you set up a spreadsheet the way you stated it above? Is
>> Column A formatted for dates? Is Column B formatted for time using the
>> HH:MM format?
>>      Format for entering a formula in a cell:
>> 1. Always begin the formula with an equal sign =.
>> 2. Enter A2 after the equal sign.
>> 3. Enter an asterisk after the A2.
>> 4. Enter the hourly rate R followed by the Enter key.
>>      Do this in cell H2. Then use the up key to move the outlined
>> rectangle back to H2. The formula in the window should be
>> =A2*R. (Yes, it is this simple.)
>>
>
> Not quite that simple, unfortunately. If the cells are formatted as HH:MM
> (as Amichai says they are), the numeric value of the cell is the fraction of
> a day that time represents, not the number of minutes. So what appears as
> 00:30 (30 minutes) is actually 0.02083333... (=30/(24*60)) as far as any
> formula is concerned.
>
> =A2*60*24*(Rate_per_minute)
> should do it - A2*60*24 converts the time into a number of minutes, then
> multiplied by your minutely rate. I'm not sure how reliable that would be if
> A2 is greater than 24 hours, but that probably isn't an issue to you (unless
> you get a lot of very long calls!)
>
> You may want to put the minutely rate in another cell, say J1, and use the
> following in H2:
> =A2*60*24*$J$1
> Then you can easily change the rate for each customer. Using $J$2 (instead
> of just J1) ensures that J1 is used in every line when you copy the formula
> to other cells (as Dan describes below).
>
>
>       Entering a formula into adjacent cells:
>> 1. Place the outlined rectangle in the first cell.
>> 2. Move the cursor over the small square at the bottom right corner
>> (vortex) or the outlined rectangle. The cursor becomes a small plus sign
>> +.
>> 3. Drag and drop the plus sign + to the last cell.
>>      This process can be used to copy a formula in a vertical or
>> horizontal range of cells.
>>      Begin at cell H2 and drag down to H32. Click any of these cells to
>> see the formula entered into that cell.
>>      Another suggestion: Do this for one sheet. Also enter the Headings
>> in cells A1, B1, and H1. Now highlight a block of cells containing
>> columns A through H and rows 1 through 32. A1 should be at the top left
>> of this block of cells, and H32 should be at the bottom right of it.
>> Copy this. In each of the sheets, paste this block of cells into cell
>> A1. You have now formated the sheets and entered the formulas in column
>> H.
>>      Now to modify the formulas of each sheet: click cell H2. The
>> formula appears in the window above the column headers. Change the rate
>> to what you want in that sheet and click the green check mark to the
>> left of the window. (It only appears after you change something in this
>> window.) Now drag and drop the formula down to cell H32. Do this for all
>> the cells. You should be good to go.
>>
>> Dan
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

openoffice.mbourne
I missed the bit saying it's an hourly rate, and assumed minutely rate
since you mentioned the time being in minutes; my mistake there. Also
used column A instead of B for the time. In that case, putting in cell H2:
=B2*24*150
will give the cost of the call logged in that line - B2*24 converts the
time into a number of hours, then multiplied by your hourly rate (150).

Again, I would suggest putting the hourly rate in a separate cell and
referring to that, so that it is easy to change. Otherwise, you have to
remember to copy the formula down each time the hourly rate is changed.
So, with the hourly rate (150 in your example) in cell J1, you'd have in H2:
=B2*24*$J$1

To get the total in H1, simply add up all the rows:
=SUM(H2:H65536)

If you don't want the cost of each line itemised, it should be possible
to add up the times and then multiply that total by the hourly rate:
=SUM(B2:B65536)*24*150
or:
=SUM(B2:B65536)*24*$J$1

Personally, I'd rather have the cost of each line as well as the total
though, as it would be easier to track down errors in the data (e.g. if
the total is ridiculously large, which line is the problem in).

Hope that helps.

Mark.


Amichai Rotman - [hidden email] wrote:

> Thanks guys,
>
> But - none of it worked...
>
> I'll describe my needs again(disregard the last one):
>
> I have a customer with an hourly rate of 150. Here is what I entered in the
> sheet:
>
> A1: Date (Header) - Column A formated as Date (DD-MM-YYYY)
> B1: Time (Header) - Column B formated as Time (HH:MM)
> G1: Subtotal - how much to charge this month (Header)
>
> A2: The first line with data - the date of the first call this month
> B2: The duration of the call - 00:45 (forty five minutes)
>
> What I want to achieve:
>
> H1 should show me how much I have earned from this customer till now, so I
> need it to multiply 150 by the time I spent for each line (B2:B32)*150 - or
> something like that...
>
> Until now I done this with a pocket calculator, for example:
>
> 0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of  188.50
>
> I just want H1 to reflect that sum if I calculate 150 * (B2:B32)
>
> I hope it is now clearer...
>
> Thanks!
>
> Amichai.
>
>
> On Thu, Jan 13, 2011 at 21:33,<[hidden email]>  wrote:
>
>> Dan Lewis - [hidden email] wrote:
>>
>>> On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:
>>>
>>>> Hey All,
>>>>
>>>> I have a bunch of customers I give phone support to.
>>>>
>>>> I'd like to create a spreadsheet to calculate how much I should charge
>>>> them
>>>> each month. Each customer has a different hourly rate.
>>>>
>>>> So, here is what I want to do:
>>>>
>>>> Sheet per customer
>>>> For each customer I enter the following:
>>>>
>>>> Column A: Date
>>>> Column B: Time (in minutes - HH:MM, 24hrs format)
>>>> Column H: A formula to multiply the contents of B2:B32 times hourly
>>>> rate....
>>>>
>>>> Obviously I need help with that formula...
>>>>
>>>> Thanks!
>>>>
>>>> Amichai.
>>>>
>>>
>>>       Have you set up a spreadsheet the way you stated it above? Is
>>> Column A formatted for dates? Is Column B formatted for time using the
>>> HH:MM format?
>>>       Format for entering a formula in a cell:
>>> 1. Always begin the formula with an equal sign =.
>>> 2. Enter A2 after the equal sign.
>>> 3. Enter an asterisk after the A2.
>>> 4. Enter the hourly rate R followed by the Enter key.
>>>       Do this in cell H2. Then use the up key to move the outlined
>>> rectangle back to H2. The formula in the window should be
>>> =A2*R. (Yes, it is this simple.)
>>>
>>
>> Not quite that simple, unfortunately. If the cells are formatted as HH:MM
>> (as Amichai says they are), the numeric value of the cell is the fraction of
>> a day that time represents, not the number of minutes. So what appears as
>> 00:30 (30 minutes) is actually 0.02083333... (=30/(24*60)) as far as any
>> formula is concerned.
>>
>> =A2*60*24*(Rate_per_minute)
>> should do it - A2*60*24 converts the time into a number of minutes, then
>> multiplied by your minutely rate. I'm not sure how reliable that would be if
>> A2 is greater than 24 hours, but that probably isn't an issue to you (unless
>> you get a lot of very long calls!)
>>
>> You may want to put the minutely rate in another cell, say J1, and use the
>> following in H2:
>> =A2*60*24*$J$1
>> Then you can easily change the rate for each customer. Using $J$2 (instead
>> of just J1) ensures that J1 is used in every line when you copy the formula
>> to other cells (as Dan describes below).
>>
>>
>>        Entering a formula into adjacent cells:
>>> 1. Place the outlined rectangle in the first cell.
>>> 2. Move the cursor over the small square at the bottom right corner
>>> (vortex) or the outlined rectangle. The cursor becomes a small plus sign
>>> +.
>>> 3. Drag and drop the plus sign + to the last cell.
>>>       This process can be used to copy a formula in a vertical or
>>> horizontal range of cells.
>>>       Begin at cell H2 and drag down to H32. Click any of these cells to
>>> see the formula entered into that cell.
>>>       Another suggestion: Do this for one sheet. Also enter the Headings
>>> in cells A1, B1, and H1. Now highlight a block of cells containing
>>> columns A through H and rows 1 through 32. A1 should be at the top left
>>> of this block of cells, and H32 should be at the bottom right of it.
>>> Copy this. In each of the sheets, paste this block of cells into cell
>>> A1. You have now formated the sheets and entered the formulas in column
>>> H.
>>>       Now to modify the formulas of each sheet: click cell H2. The
>>> formula appears in the window above the column headers. Change the rate
>>> to what you want in that sheet and click the green check mark to the
>>> left of the window. (It only appears after you change something in this
>>> window.) Now drag and drop the formula down to cell H32. Do this for all
>>> the cells. You should be good to go.
>>>
>>> Dan
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

Richard Saunders
Why are you sending these e-mails to me?

R

On 1/14/2011 9:09 AM, [hidden email] wrote:

> I missed the bit saying it's an hourly rate, and assumed minutely rate
> since you mentioned the time being in minutes; my mistake there. Also
> used column A instead of B for the time. In that case, putting in cell
> H2:
> =B2*24*150
> will give the cost of the call logged in that line - B2*24 converts
> the time into a number of hours, then multiplied by your hourly rate
> (150).
>
> Again, I would suggest putting the hourly rate in a separate cell and
> referring to that, so that it is easy to change. Otherwise, you have
> to remember to copy the formula down each time the hourly rate is
> changed. So, with the hourly rate (150 in your example) in cell J1,
> you'd have in H2:
> =B2*24*$J$1
>
> To get the total in H1, simply add up all the rows:
> =SUM(H2:H65536)
>
> If you don't want the cost of each line itemised, it should be
> possible to add up the times and then multiply that total by the
> hourly rate:
> =SUM(B2:B65536)*24*150
> or:
> =SUM(B2:B65536)*24*$J$1
>
> Personally, I'd rather have the cost of each line as well as the total
> though, as it would be easier to track down errors in the data (e.g.
> if the total is ridiculously large, which line is the problem in).
>
> Hope that helps.
>
> Mark.
>
>
> Amichai Rotman - [hidden email] wrote:
>> Thanks guys,
>>
>> But - none of it worked...
>>
>> I'll describe my needs again(disregard the last one):
>>
>> I have a customer with an hourly rate of 150. Here is what I entered
>> in the
>> sheet:
>>
>> A1: Date (Header) - Column A formated as Date (DD-MM-YYYY)
>> B1: Time (Header) - Column B formated as Time (HH:MM)
>> G1: Subtotal - how much to charge this month (Header)
>>
>> A2: The first line with data - the date of the first call this month
>> B2: The duration of the call - 00:45 (forty five minutes)
>>
>> What I want to achieve:
>>
>> H1 should show me how much I have earned from this customer till now,
>> so I
>> need it to multiply 150 by the time I spent for each line
>> (B2:B32)*150 - or
>> something like that...
>>
>> Until now I done this with a pocket calculator, for example:
>>
>> 0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of  
>> 188.50
>>
>> I just want H1 to reflect that sum if I calculate 150 * (B2:B32)
>>
>> I hope it is now clearer...
>>
>> Thanks!
>>
>> Amichai.
>>
>>
>> On Thu, Jan 13, 2011 at 21:33,<[hidden email]>  
>> wrote:
>>
>>> Dan Lewis - [hidden email] wrote:
>>>
>>>> On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:
>>>>
>>>>> Hey All,
>>>>>
>>>>> I have a bunch of customers I give phone support to.
>>>>>
>>>>> I'd like to create a spreadsheet to calculate how much I should
>>>>> charge
>>>>> them
>>>>> each month. Each customer has a different hourly rate.
>>>>>
>>>>> So, here is what I want to do:
>>>>>
>>>>> Sheet per customer
>>>>> For each customer I enter the following:
>>>>>
>>>>> Column A: Date
>>>>> Column B: Time (in minutes - HH:MM, 24hrs format)
>>>>> Column H: A formula to multiply the contents of B2:B32 times hourly
>>>>> rate....
>>>>>
>>>>> Obviously I need help with that formula...
>>>>>
>>>>> Thanks!
>>>>>
>>>>> Amichai.
>>>>>
>>>>
>>>>       Have you set up a spreadsheet the way you stated it above? Is
>>>> Column A formatted for dates? Is Column B formatted for time using the
>>>> HH:MM format?
>>>>       Format for entering a formula in a cell:
>>>> 1. Always begin the formula with an equal sign =.
>>>> 2. Enter A2 after the equal sign.
>>>> 3. Enter an asterisk after the A2.
>>>> 4. Enter the hourly rate R followed by the Enter key.
>>>>       Do this in cell H2. Then use the up key to move the outlined
>>>> rectangle back to H2. The formula in the window should be
>>>> =A2*R. (Yes, it is this simple.)
>>>>
>>>
>>> Not quite that simple, unfortunately. If the cells are formatted as
>>> HH:MM
>>> (as Amichai says they are), the numeric value of the cell is the
>>> fraction of
>>> a day that time represents, not the number of minutes. So what
>>> appears as
>>> 00:30 (30 minutes) is actually 0.02083333... (=30/(24*60)) as far as
>>> any
>>> formula is concerned.
>>>
>>> =A2*60*24*(Rate_per_minute)
>>> should do it - A2*60*24 converts the time into a number of minutes,
>>> then
>>> multiplied by your minutely rate. I'm not sure how reliable that
>>> would be if
>>> A2 is greater than 24 hours, but that probably isn't an issue to you
>>> (unless
>>> you get a lot of very long calls!)
>>>
>>> You may want to put the minutely rate in another cell, say J1, and
>>> use the
>>> following in H2:
>>> =A2*60*24*$J$1
>>> Then you can easily change the rate for each customer. Using $J$2
>>> (instead
>>> of just J1) ensures that J1 is used in every line when you copy the
>>> formula
>>> to other cells (as Dan describes below).
>>>
>>>
>>>        Entering a formula into adjacent cells:
>>>> 1. Place the outlined rectangle in the first cell.
>>>> 2. Move the cursor over the small square at the bottom right corner
>>>> (vortex) or the outlined rectangle. The cursor becomes a small plus
>>>> sign
>>>> +.
>>>> 3. Drag and drop the plus sign + to the last cell.
>>>>       This process can be used to copy a formula in a vertical or
>>>> horizontal range of cells.
>>>>       Begin at cell H2 and drag down to H32. Click any of these
>>>> cells to
>>>> see the formula entered into that cell.
>>>>       Another suggestion: Do this for one sheet. Also enter the
>>>> Headings
>>>> in cells A1, B1, and H1. Now highlight a block of cells containing
>>>> columns A through H and rows 1 through 32. A1 should be at the top
>>>> left
>>>> of this block of cells, and H32 should be at the bottom right of it.
>>>> Copy this. In each of the sheets, paste this block of cells into cell
>>>> A1. You have now formated the sheets and entered the formulas in
>>>> column
>>>> H.
>>>>       Now to modify the formulas of each sheet: click cell H2. The
>>>> formula appears in the window above the column headers. Change the
>>>> rate
>>>> to what you want in that sheet and click the green check mark to the
>>>> left of the window. (It only appears after you change something in
>>>> this
>>>> window.) Now drag and drop the formula down to cell H32. Do this
>>>> for all
>>>> the cells. You should be good to go.
>>>>
>>>> Dan
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>>
>>>>
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

openoffice.mbourne
Richard Saunders wrote:
> Why are you sending these e-mails to me?
>
> R

I'm not; I'm sending them to the openoffice.org users' mailing list, to
which you appear to be subscribed. This is a mailing list for users of
the openoffice.org software to help each other with problems.

If you no longer wish to receive emails for this mailing list, you can
unsubscribe yourself by following the instructions at the bottom of most
emails sent via the list (including mine, to which you replied):
 > To unsubscribe, e-mail: [hidden email]
 > For additional commands, e-mail: [hidden email]

This occasionally doesn't work quite right, and there are others here
who can help in that case. Please reply to users(at)openoffice.org
(replacing (at) with @) and I'm sure someone able to help will do so.

Mark.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Customer Billing Formula

Amichai Rotman
In reply to this post by Richard Saunders
Thanks Mark!

That one did the trick!

Now I have a file with a sheet per customer with their hourly rate and I can
find out at a glance how much I've mde till now and how much I'm supposed to
make by the end of the month.

Amichai.

On Fri, Jan 14, 2011 at 18:19, Richard Saunders <[hidden email]> wrote:

> Why are you sending these e-mails to me?
>
> R
>
>
> On 1/14/2011 9:09 AM, [hidden email] wrote:
>
>> I missed the bit saying it's an hourly rate, and assumed minutely rate
>> since you mentioned the time being in minutes; my mistake there. Also used
>> column A instead of B for the time. In that case, putting in cell H2:
>> =B2*24*150
>> will give the cost of the call logged in that line - B2*24 converts the
>> time into a number of hours, then multiplied by your hourly rate (150).
>>
>> Again, I would suggest putting the hourly rate in a separate cell and
>> referring to that, so that it is easy to change. Otherwise, you have to
>> remember to copy the formula down each time the hourly rate is changed. So,
>> with the hourly rate (150 in your example) in cell J1, you'd have in H2:
>> =B2*24*$J$1
>>
>> To get the total in H1, simply add up all the rows:
>> =SUM(H2:H65536)
>>
>> If you don't want the cost of each line itemised, it should be possible to
>> add up the times and then multiply that total by the hourly rate:
>> =SUM(B2:B65536)*24*150
>> or:
>> =SUM(B2:B65536)*24*$J$1
>>
>> Personally, I'd rather have the cost of each line as well as the total
>> though, as it would be easier to track down errors in the data (e.g. if the
>> total is ridiculously large, which line is the problem in).
>>
>> Hope that helps.
>>
>> Mark.
>>
>>
>> Amichai Rotman - [hidden email] wrote:
>>
>>> Thanks guys,
>>>
>>> But - none of it worked...
>>>
>>> I'll describe my needs again(disregard the last one):
>>>
>>> I have a customer with an hourly rate of 150. Here is what I entered in
>>> the
>>> sheet:
>>>
>>> A1: Date (Header) - Column A formated as Date (DD-MM-YYYY)
>>> B1: Time (Header) - Column B formated as Time (HH:MM)
>>> G1: Subtotal - how much to charge this month (Header)
>>>
>>> A2: The first line with data - the date of the first call this month
>>> B2: The duration of the call - 00:45 (forty five minutes)
>>>
>>> What I want to achieve:
>>>
>>> H1 should show me how much I have earned from this customer till now, so
>>> I
>>> need it to multiply 150 by the time I spent for each line (B2:B32)*150 -
>>> or
>>> something like that...
>>>
>>> Until now I done this with a pocket calculator, for example:
>>>
>>> 0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of  188.50
>>>
>>> I just want H1 to reflect that sum if I calculate 150 * (B2:B32)
>>>
>>> I hope it is now clearer...
>>>
>>> Thanks!
>>>
>>> Amichai.
>>>
>>>
>>> On Thu, Jan 13, 2011 at 21:33,<[hidden email]>
>>>  wrote:
>>>
>>>  Dan Lewis - [hidden email] wrote:
>>>>
>>>>  On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote:
>>>>>
>>>>>  Hey All,
>>>>>>
>>>>>> I have a bunch of customers I give phone support to.
>>>>>>
>>>>>> I'd like to create a spreadsheet to calculate how much I should charge
>>>>>> them
>>>>>> each month. Each customer has a different hourly rate.
>>>>>>
>>>>>> So, here is what I want to do:
>>>>>>
>>>>>> Sheet per customer
>>>>>> For each customer I enter the following:
>>>>>>
>>>>>> Column A: Date
>>>>>> Column B: Time (in minutes - HH:MM, 24hrs format)
>>>>>> Column H: A formula to multiply the contents of B2:B32 times hourly
>>>>>> rate....
>>>>>>
>>>>>> Obviously I need help with that formula...
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>> Amichai.
>>>>>>
>>>>>>
>>>>>      Have you set up a spreadsheet the way you stated it above? Is
>>>>> Column A formatted for dates? Is Column B formatted for time using the
>>>>> HH:MM format?
>>>>>      Format for entering a formula in a cell:
>>>>> 1. Always begin the formula with an equal sign =.
>>>>> 2. Enter A2 after the equal sign.
>>>>> 3. Enter an asterisk after the A2.
>>>>> 4. Enter the hourly rate R followed by the Enter key.
>>>>>      Do this in cell H2. Then use the up key to move the outlined
>>>>> rectangle back to H2. The formula in the window should be
>>>>> =A2*R. (Yes, it is this simple.)
>>>>>
>>>>>
>>>> Not quite that simple, unfortunately. If the cells are formatted as
>>>> HH:MM
>>>> (as Amichai says they are), the numeric value of the cell is the
>>>> fraction of
>>>> a day that time represents, not the number of minutes. So what appears
>>>> as
>>>> 00:30 (30 minutes) is actually 0.02083333... (=30/(24*60)) as far as any
>>>> formula is concerned.
>>>>
>>>> =A2*60*24*(Rate_per_minute)
>>>> should do it - A2*60*24 converts the time into a number of minutes, then
>>>> multiplied by your minutely rate. I'm not sure how reliable that would
>>>> be if
>>>> A2 is greater than 24 hours, but that probably isn't an issue to you
>>>> (unless
>>>> you get a lot of very long calls!)
>>>>
>>>> You may want to put the minutely rate in another cell, say J1, and use
>>>> the
>>>> following in H2:
>>>> =A2*60*24*$J$1
>>>> Then you can easily change the rate for each customer. Using $J$2
>>>> (instead
>>>> of just J1) ensures that J1 is used in every line when you copy the
>>>> formula
>>>> to other cells (as Dan describes below).
>>>>
>>>>
>>>>       Entering a formula into adjacent cells:
>>>>
>>>>> 1. Place the outlined rectangle in the first cell.
>>>>> 2. Move the cursor over the small square at the bottom right corner
>>>>> (vortex) or the outlined rectangle. The cursor becomes a small plus
>>>>> sign
>>>>> +.
>>>>> 3. Drag and drop the plus sign + to the last cell.
>>>>>      This process can be used to copy a formula in a vertical or
>>>>> horizontal range of cells.
>>>>>      Begin at cell H2 and drag down to H32. Click any of these cells to
>>>>> see the formula entered into that cell.
>>>>>      Another suggestion: Do this for one sheet. Also enter the Headings
>>>>> in cells A1, B1, and H1. Now highlight a block of cells containing
>>>>> columns A through H and rows 1 through 32. A1 should be at the top left
>>>>> of this block of cells, and H32 should be at the bottom right of it.
>>>>> Copy this. In each of the sheets, paste this block of cells into cell
>>>>> A1. You have now formated the sheets and entered the formulas in column
>>>>> H.
>>>>>      Now to modify the formulas of each sheet: click cell H2. The
>>>>> formula appears in the window above the column headers. Change the rate
>>>>> to what you want in that sheet and click the green check mark to the
>>>>> left of the window. (It only appears after you change something in this
>>>>> window.) Now drag and drop the formula down to cell H32. Do this for
>>>>> all
>>>>> the cells. You should be good to go.
>>>>>
>>>>> Dan
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>>
>>>>
>>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>