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 <[email protected]> wrote: > Why are you sending these e-mails to me? > > R > > > On 1/14/2011 9:09 AM, [email protected] 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 - [email protected] 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,<[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] >>>> >>>> >>>> >>> >> >> --------------------------------------------------------------------- >> 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] > >
