# Customer Billing Formula Classic List Threaded 8 messages Open this post in threaded view
|

## Customer Billing Formula

 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.
Open this post in threaded view
|

## Re: Customer Billing Formula

 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]
Open this post in threaded view
|

## Re: Customer Billing Formula

 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]
Open this post in threaded view
|

## Re: Customer Billing Formula

Open this post in threaded view
|

## Re: Customer Billing Formula

Open this post in threaded view
|