Question about Microsoft Excel 2003 (06503995)

Re: excel argument

A bit late but a solution for those who have a similar problem. Could also be the first step in understanding how to use Functions!

Create an input cell (cell a for the demonstration) and an output cell (cell b for the demonstration). These cells will have actual references like A11 or C23 etc.

Enter the total hours worked (eg 54).

Create some data manipulation cells out to the right where you can later Hide the Columns (for neatness) (cells c and d for the demonstration).

In the output cell (cell b) write =if("cell a"<=40,cell c,cell d).

In "cell c" write =cell a*Standard Rate (where cell a is the hours input cell reference and Standard Rate is the hourly rate without Overtime).

In cell d write =sum(40*Standard Rate)+sum((cell a-40)*Overtime Rate))

Note: Standard Rate and Overtime Rate can be separate input cells (where you can enter the Hourly rates for standard and overtime hours) to make future calculations easier when these rates change. Note: In the formula you will use cell references to these input cells rather than the dollar amount.

This will give you a total of dollars earned for any hours input.

You could split the standard and overtime dollars up into 2 cells to get a breakdown of costs if you required and then combine these to get the total (as above).

The trick here is NOT to attempt a single complex formula to do the calculation.

Use as many steps as you need to keep it simple in your mind (eg 2+3=5 is much easier to understand than ((((7*6)+2)/(8118/369)+(125/(5*5))=5. Each of the steps in this complex calculation can be broken down into simple Arithmetic formulas (eg (5*5) represents 5x5=25) and then the result can be used in another simple calculation as a cell reference and then that result can be used in a simple calculation as a cell reference and so on. I use cells out to the right of the workspace so that I can Hide the Columns for neatness and easy retrieval for later amendments.

Hope this helps someone.

Posted on Apr 08, 2008

Re: excel argument

If I understand correctly, you want to figure the normal wages at 40 hours and less. If there is more then 40 hours, calculate the normal 40 hours, then calculate the hours overtime (time and a half) and add them to get a total.

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

Posted on Dec 15, 2007

Re: excel argument

Download an excel template from here.

http://office.microsoft.com/en-us/results.aspx?qu=+write+an+argument+to+calulate+hours+worked+

Posted on Oct 23, 2007

Here is how you could do the formula with two one column having the hours of each day in it and the overtime being in another cell:

Example:

Column A Column B

8

8

8

2

8

8

0

Total 40 OverTime 2

Formula in this cell where 40 is at:

=IF(SUM(A1:A7)>40,40,SUM(A1:A7))

Formula in cell where 2 is at:

=IF(A8>40,0,SUM(A1:A7)-A8)

Let me know if this helps.

Example:

Column A Column B

8

8

8

2

8

8

0

Total 40 OverTime 2

Formula in this cell where 40 is at:

=IF(SUM(A1:A7)>40,40,SUM(A1:A7))

Formula in cell where 2 is at:

=IF(A8>40,0,SUM(A1:A7)-A8)

Let me know if this helps.

Jan 01, 2010 | Microsoft Excel 2007 Home and Student...

Please be more clear with the question.. Do you want me to analyze the formula?

Feb 21, 2008 | Microsoft Excel for PC

The solution would be to have an input column(e.g. A) that is separate to the hours and overtime columns. Then in the hours column enter =IF(A1<40,A1,40) and in the overtime column =IF(A1<41,0,A1-40)

Oct 28, 2009 | Microsoft Excel for PC

what is the formula

May 19, 2009 | Microsoft Excel for PC

Format both cells with the time format "h:mm AM/PM".

Lets say the start time is in cell A2 and the stop time is in cell B2. In cell C2 put the formula =B2-A2 and custom format the cell C2 as: "h:mm"

Lets say the start time is in cell A2 and the stop time is in cell B2. In cell C2 put the formula =B2-A2 and custom format the cell C2 as: "h:mm"

Apr 16, 2009 | Microsoft Excel for PC

=IF(AND($C$3<=($B$3 * 0.3), E3<2), "yes", "No")

Mar 14, 2009 | Microsoft Office Excel 2007

Hello,

The formula should go like this:

=IF(C11>40,(C11-40)*D11,0)

This one will give you the result of (ONLY overtime hours)*(hourly wage), and if there are no overtime hours the result is 0.

Now, if you need to multiply that result with say 1,5 or whatever - insert the number you need like this:

=IF(C11>40,(C11-40)*D11*1.5,0)

If you need more help, please ask.

The formula should go like this:

=IF(C11>40,(C11-40)*D11,0)

This one will give you the result of (ONLY overtime hours)*(hourly wage), and if there are no overtime hours the result is 0.

Now, if you need to multiply that result with say 1,5 or whatever - insert the number you need like this:

=IF(C11>40,(C11-40)*D11*1.5,0)

If you need more help, please ask.

Sep 14, 2008 | Business & Productivity Software

You should be able to find what you want here:

http://www.usd.edu/trio/tut/excel/

http://www.usd.edu/trio/tut/excel/

Aug 10, 2008 | Microsoft Excel for PC

instead of validation use conditional formating (under format menu). If c1<1 format cell as red. It does not have the attention getter that validation does not require the active cell event. Every other option requires the cell become active for a test.

Apr 13, 2008 | Business & Productivity Software

mmm...could be tricky......you could try the argument IF THEN ELSE in the functions list, in conjunction with additional columns to carry out the intermediate calculations, then you can hide those columns. perhaps you could also make use of conditional formatting. any problems come back to me.....could you place a snapshot of the sheet you are designing ? just highlight the cells , copy them, and use edit "paste special", tick values when pasting into this forum.

Jan 18, 2008 | Business & Productivity Software

264 people viewed this question

Usually answered in minutes!

×