Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel formulae
i'm creating invoices from cost sheets.
in order to hide the 'true' mark up (profit) i have to inflate the labour (time taken) however how more oftenthan not i end up with a figure that is not divisible by a time unit such as, 3 hours, or 3.25 hours, 4.75 hous ......must be quarter of an hour increases etc. what formulae might one create, to make a monatary figure relate to a time figure in .25 units (15 minutes) example a sum of £88.23 divided by labour rate of £27.00 = 3.26666666 ideally this time figure should be 3.25 hours or, 3.5 hours.... ideas? regards Dean |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel formulae
"valleyboy" wrote:
example a sum of £88.23 divided by labour rate of £27.00 = 3.26666666 ideally this time figure should be 3.25 hours or, 3.5 hours.... I think you want to round to the nearest 0.25 hours. One way: =round(4 * 88.23/27.00, 0) / 4 If you want to always round up or down the rate, use ROUNDUP or ROUNDDOWN. ----- original message ----- "valleyboy" wrote: i'm creating invoices from cost sheets. in order to hide the 'true' mark up (profit) i have to inflate the labour (time taken) however how more oftenthan not i end up with a figure that is not divisible by a time unit such as, 3 hours, or 3.25 hours, 4.75 hous .....must be quarter of an hour increases etc. what formulae might one create, to make a monatary figure relate to a time figure in .25 units (15 minutes) example a sum of £88.23 divided by labour rate of £27.00 = 3.26666666 ideally this time figure should be 3.25 hours or, 3.5 hours.... ideas? regards Dean |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel formulae
Mmm, increasing time taken won't make you attractive in comparison to your
competition (though there may be no means of comparison). :-) I may be being naive here or misunderstanding your application, but I'm not sure why you don't just use a hidden column or row, or a cell outside the print range for the markup %? You could reproduce your supplier invoice figures into an input area of the screen and the output (or your clients invoice) is the print range (can be another worksheet/tab), in which your formulae apply the markup and calculate the extended price to your client. if you choose to use another worksheet/tab for the output, creating range names for the cells containing the input data makes it easier to write formulae in another worksheet/tab that references those cells. There are a couple of practical devices that can help, especially if employees other than you use the system. For example, I often leave all my input cells uncoloured with a box around and input characters appear in blue. They are the only cells I leave unprotected. The output area I colour in (say) pale yellow, and the calculated information appears in standard black print. That way, formulae are protected from corruption and it is immediately clear where the user keys the data. Going into Tools/options and clearing the column and row headers and gridlines can tidy it up and make it less confusing for other users too. In the case of an invoice, I would record a macro when I print the print range for the first time, and attach it to a button. I use Excel 2000 so it is probably of no use me giving you the exact menu routines, but they are all quite basic. Hope you get something useful out of all this. :-) Cheers "valleyboy" wrote: i'm creating invoices from cost sheets. in order to hide the 'true' mark up (profit) i have to inflate the labour (time taken) however how more oftenthan not i end up with a figure that is not divisible by a time unit such as, 3 hours, or 3.25 hours, 4.75 hous .....must be quarter of an hour increases etc. what formulae might one create, to make a monatary figure relate to a time figure in .25 units (15 minutes) example a sum of £88.23 divided by labour rate of £27.00 = 3.26666666 ideally this time figure should be 3.25 hours or, 3.5 hours.... ideas? regards Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formulae | Excel Discussion (Misc queries) | |||
Formulae: Paste value formulae after doing an average operation | Excel Discussion (Misc queries) | |||
using formulae in excel | Excel Worksheet Functions | |||
excel formulae | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions |