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, <[email protected]> wrote: > Dan Lewis - [email protected] 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: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> >> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
