Question about Microsoft Excel for PC

I have created an index formula (with reference). In the function argument box it returns the formula result as I know it should be (5.09). However in the actual cell, another value is appearing (6.25)

I have no idea why it is showing correct in the function arguments box but not in the cell. Has anybody got any answers please??

Many thanks!

Ad

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of.(from cars to computers, handyman, and even drones)

click here to download the app (for users in the US for now) and get all the help you need.

Goodluck!

Posted on Jan 02, 2017

Ad

Take for example a function y=f(x). What is inside the function is called its argument (what it is acting on). If you get that error it may mean that the argument type is wrong or that it is out of range. Here are two simple examples.

- A function requires a real number and you make it act on a complex number (wrong argument type).
- You know that the log () function must have a positive argument and you try to calculate say log(-2.5).
- Arc sine and arc cosine must have an argument in the closed interval [-1,1] but you call them with a value outside that interval.
- A function is defined with a list of arguments yet it is called with more items in the list or less items in the list.

Apr 09, 2013 | Casio Fx-5800p Programmable Scientific...

Correct a #N/A error
Show All
Hide All
This error occurs when a value is not available to a function or formula.

- Optionally, click the cell that displays the error, click the button that appears , and then click
**Show Calculation Steps**if it appears. - Review the following possible causes and solutions.
Missing 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.

Giving 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. Using 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.

Using 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).

Omitting 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.

Using 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.

Running 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

Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.

Formulas used in Excel must follow a certain syntax.

- All formulas begin with an equals sign
**(=)**. - Some formulas use operands such as
**+,-, *,/**for addition, subtraction, multiplication or division.

For example, the formula =A1+A2+A3 would add the contents of cells A1, A2 and A3. - Other formulas refer to different functions such as SUM, AVERAGE and others.

For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3. - Formulas can be
**combined with operands.**

For example, the formula =10*SUM(A1:A3) would add the contents cells A1 through A3 and multiply them by 10. - Functions can
**be nested within each other.**

For example, the formula =SQRT(10*SUM(A1:A3)) would take the square root of ten times the sum of cells A1 through A3. When functions are nested, it is important that the number of left parentheses match the number of right parentheses.

Aug 19, 2011 | Microsoft EXCEL 2004 for Mac

Step 1

Determine what formula is to be copied and to what location it is to be copied. Carefully consider the cells that are to be referenced in the formula and that they are indeed the cells that contain the information to be acted upon by this formula.

Step 2

Highlight a cell that has the formula in it and click the right mouse button. Select the "Copy" option.

Step 3

Move to the new destination cell for this formula, right click the mouse button and select "Paste."

Step 4

Check the first cell to make sure the correct calculation has been performed. If not, check the formula that appears in the cell that contains the result of the formula that was just copied. Make sure that the formula references the correct cells. If not, correct the formula and then copy the corrected formula to the rest of the cells that should have this formula applied to them.

The only time I have seen this not work is when the cells/pages are protected.

Note:

Some formulas will require references to multiple pages or a workbook. Make sure that the absolute cell address is used for specific numbers and those that are to be applied to subsequent cell addresses are clearly denoted by the use of "$" in the cell address.

Determine what formula is to be copied and to what location it is to be copied. Carefully consider the cells that are to be referenced in the formula and that they are indeed the cells that contain the information to be acted upon by this formula.

Step 2

Highlight a cell that has the formula in it and click the right mouse button. Select the "Copy" option.

Step 3

Move to the new destination cell for this formula, right click the mouse button and select "Paste."

Step 4

Check the first cell to make sure the correct calculation has been performed. If not, check the formula that appears in the cell that contains the result of the formula that was just copied. Make sure that the formula references the correct cells. If not, correct the formula and then copy the corrected formula to the rest of the cells that should have this formula applied to them.

The only time I have seen this not work is when the cells/pages are protected.

Note:

Some formulas will require references to multiple pages or a workbook. Make sure that the absolute cell address is used for specific numbers and those that are to be applied to subsequent cell addresses are clearly denoted by the use of "$" in the cell address.

Dec 17, 2009 | Microsoft Office Home and Student 2007...

You would have to combine the use of 2 functions. The Address and Match funbctions.

Lets say the number you want the address of is located in cell F1 and you have 2 columns of numbers. One colum in Column A and the other in column B. I will give you 2 formulas. The 1st one will return just the row number. The 2nd one will return the cell address.

Option 1: Lets say you just want to know the row reference of the number in cell F1. Place this formula in cell D1. =MATCH(F1,A1:A20)

If you have another column ytou want the row number of, place the formula in lets say cell D2 and change the column references from 'A' to 'B'.

Option 2: If you want the cell reference, place this formula in cell D1 and D2 instead of the firt formula.

=ADDRESS(MATCH(F1,A1:A20,0),1,1,TRUE)

And just like the first option, for the 2nd column, put the formula in D2 and change the column reference 'A' to 'B'.

Lets say the number you want the address of is located in cell F1 and you have 2 columns of numbers. One colum in Column A and the other in column B. I will give you 2 formulas. The 1st one will return just the row number. The 2nd one will return the cell address.

Option 1: Lets say you just want to know the row reference of the number in cell F1. Place this formula in cell D1. =MATCH(F1,A1:A20)

If you have another column ytou want the row number of, place the formula in lets say cell D2 and change the column references from 'A' to 'B'.

Option 2: If you want the cell reference, place this formula in cell D1 and D2 instead of the firt formula.

=ADDRESS(MATCH(F1,A1:A20,0),1,1,TRUE)

And just like the first option, for the 2nd column, put the formula in D2 and change the column reference 'A' to 'B'.

Feb 17, 2009 | Microsoft Excel for PC

Add another colum, say D with result of B*C

Do a list of the different cat, and use sumif

For cat 0101 (in cell F1)

formula to put in G1

=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Do a list of the different cat, and use sumif

For cat 0101 (in cell F1)

formula to put in G1

=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Feb 16, 2009 | Microsoft Excel for PC

Clicking cancel will invalidate the formulae, circular references refer to the dependant and precedent cells using each other.

Track/ Audit depending on your Excel version to show which formulae is incorrect - or options view, tick show formaulae and remove them.

Track/ Audit depending on your Excel version to show which formulae is incorrect - or options view, tick show formaulae and remove them.

Nov 20, 2008 | Microsoft Excel for PC

Hi shamsherpate

**In order to remove this error message you need to locate and remove the incorrect references:**

On the Formulas tab, in the Formula Auditing group, click the arrow on the Error Checking in-group button, point to Circular References, and then click the first cell listed in the submenu.

Tip You can move between cells in a circular reference by double-clicking the tracer arrows.

Review the formula in the cell. If you cannot determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu.

Note The status bar displays the word "Circular References" followed by a reference to one of the cells contained in the circular reference.

Continue to review and correct the circular reference until the status bar no longer displays the word "Circular References."

Regards,

TheAssembler

On the Formulas tab, in the Formula Auditing group, click the arrow on the Error Checking in-group button, point to Circular References, and then click the first cell listed in the submenu.

Tip You can move between cells in a circular reference by double-clicking the tracer arrows.

Review the formula in the cell. If you cannot determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu.

Note The status bar displays the word "Circular References" followed by a reference to one of the cells contained in the circular reference.

Continue to review and correct the circular reference until the status bar no longer displays the word "Circular References."

Regards,

TheAssembler

Jun 06, 2008 | Microsoft Excel for PC

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!

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!

Oct 22, 2007 | Microsoft Excel 2003 (06503995)

It is very useful to learn excel shortcut realy i thank you

Sep 26, 2007 | Microsoft Office Standard for PC

Jan 28, 2016 | Microsoft Excel for PC

Dec 12, 2013 | Microsoft Excel for PC

Jul 23, 2013 | Microsoft Excel for PC

230 people viewed this question

Usually answered in minutes!

×