Question about Microsoft Excel 2003 (06503995)

3 Answers

Excel argument I'm trying to write an argument to calulate hours worked * wages and to calulate OT if applicable. Here is my formula: wages*40 if hours worked>40,-40, and here is where i need help. I want excel to calulate the difference and add the two sums together to get wages earned. I try to use the IF argument and the array argument. I am stuck please help!

Posted by cherriipi on

Ad

3 Answers

Les Clayton

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that got 5 achievements.

    Novelist:

    An expert who has written 50 answers of more than 400 characters.

    Champion:

    An expert who has answered 200 questions.

  • Expert
  • 214 Answers

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

  • Les Clayton Apr 08, 2008

    Forgot to say:



    In the output cells formula do not write the " symbols, I should have deleted these as I used cell a etc as cell references in all the other areas. Note as said in my first reply cell a will be that cells reference eg A32 or B12.

×

Ad

Anonymous

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that got 5 achievements.

    Novelist:

    An expert who has written 50 answers of more than 400 characters.

    Governor:

    An expert whose answer got voted for 20 times.

  • Expert
  • 150 Answers

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.

excel argument - 2bef84b.png

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

Ad

Thomas Christopher

  • Level 3:

    An expert who has achieved level 3 by getting 1000 points

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Genius:

    An expert who has answered 1,000 questions.

  • Master
  • 1,601 Answers

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

2 Answers

If i work 16 hours a week how much money do i get for a 14 year oldd


Multiply hourly wage by number of hours in pay period.
The answer is your gross pay. If this is un-taxed income, you earn that much. If it is taxed, then multiply by about 0.70 to determine take home pay after taxes, although taxes vary by location.

Ex: If I work 16 hours at $9.50/hr, I make $152/week before taxes, but only take home $106.40 ($152 x 0.70) per week after the government takes its cut.

Use a calculator, hand-held or on the computer in accessories, cell phone app, or pencil and paper with simple multiplication formula Wage x Hours Worked Per Pay Period x (1.00 - Tax Rate) = Take Home Pay Per Pay Period.

Mar 17, 2015 | Refrigerators

1 Answer

My MS Excel vlookup function ASCII type table contains both upper case and lower case letters, characters like ( and % or *, and numbers. It seems to return the numerical equivalent of upper case letters...


Correct a #N/A error blueup_clv.gifShow All bluedrop_clv.gifHide All This error occurs when a value is not available to a function or formula.
  1. Optionally, click the cell that displays the error, click the button that appears ooui1_za06043871.gif, and then click Show Calculation Steps if it appears.
  2. Review the following possible causes and solutions. blueup_clv.gifMissing data, and #N/A or NA() has been entered in its place
    Replace #N/A with new data.
    Note You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.
    blueup_clv.gifGiving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function
    Make sure that the lookup_value argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) is the correct type of value — for example, a value or a cell reference, but not a range reference. blueup_clv.gifUsing the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table
    By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE. The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0.
    blueup_clv.gifUsing an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula
    If the array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) has been entered into multiple cells, make sure that the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).
    blueup_clv.gifOmitting one or more required arguments from a built-in or custom worksheet function
    Enter all arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function.
    blueup_clv.gifUsing a custom worksheet function that is not available
    Make sure that the workbook that contains the worksheet function is open and the function is working properly.
    blueup_clv.gifRunning a macro that enters a function that returns #N/A
    Make sure that the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function are correct and in the correct position.

Oct 31, 2008 | Computers & Internet

1 Answer

IF STATEMENT


Let x be the total number of hours worked. Let w be the total wage.
If x < or = 40, w=16.5 x,
Else w= 16.5*40 +26.5*(x-40)

Aug 31, 2014 | Office Equipment & Supplies

1 Answer

What is the formula to calulate refrigerant r134 a from temperature to pressure


look up refrigerant pressure/temp chart
It should also be on any set of gauges your using for work with refrigerant

Jul 01, 2013 | Interdynamics RLS-134: Refrigerant R-134a...

1 Answer

CaN not connect to itunes store because of invalid argument. what does invalid argument mean???


Many issues has arises to invalid wep key on your wireless AP "access point" to other types of issues like stuck applications and such. You might want to verify your wep kay is good then try again. If that does not work looks like you going ot need to recover the little guy.


thanks hope this works do not shoot the messenger please do leave feedback and votes.

Btw, I’m available to help over the phone in case u need at https://www.6ya.com/expert/edwy_1a56970cf1ea4620

Jun 26, 2010 | Apple iPod touch

1 Answer

I am trying to figure out a payroll formula. I


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.

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

1 Answer

Wages bill reg


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"

Apr 16, 2009 | Microsoft Excel for PC

2 Answers

Excel formula


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.

Sep 14, 2008 | Computers & Internet

1 Answer

Excel formula question


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 | Computers & Internet

Not finding what you are looking for?
Microsoft Excel 2003 (06503995) Logo

300 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

Ekse

Level 3 Expert

13434 Answers

Lee Hodgson
Lee Hodgson

Level 3 Expert

4808 Answers

ThaMp3Doctor
ThaMp3Doctor

Level 3 Expert

8597 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...