excel formulae

V

valleyboy

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
 
J

Joe User

valleyboy said:
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....
Click to expand...

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 -----
 
K

Kevryl

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
 
Top