I want to reference a cell and test for multiple outcomes and return a number. I was trying to do it using nested if functions in excel 2000 but it gets too confusing.

I am trying to write a simple estimating program. I have 10000 staples to a case. I want to put .34 in cell A1 if R31<=133 and .67 in cell A1 if R31>133 then 1 in cell A1 if R31>266 and so on 1.34 if R31>400 etc. Up to whatever the limit is. And return a 0 if nothing or 0 is in R31. Or perhaps there is a better way?

Ad

As i can understand you are using the nested if wrongly, you can use it in the macro as given below. I have written this code in a macro and checked it, the results were as you are demanding.

put the following code in a macro and exicute it to get the desired results.

==================================================

If Range("R31").Value <= 133 Then

Range("A1").Value = 0.34

ElseIf Range("R31").Value > 133 And Range("R31").Value <= 266 Then

Range("A1").Value = 0.67

ElseIf Range("R31").Value > 266 And Range("R31").Value <= 400 Then

Range("A1").Value = 1

ElseIf Range("R31").Value > 400 Then

Range("A1").Value = 1.34

Else

Range("A1").Value = 0

End If

=================================================

Hope your problem will be resolved, if you still have any problem, you can ask or consult without any hesitation.

Best regards.

Posted on May 25, 2009

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.

New users get to try the service completely Free afterwhich it costs $6 per call 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

The "&" symbol is commonly known as "ampersand" and it is a calculation operator. Ampersand can be used in Excel 2010 to join text items from different cells. It functions much similar as "CONCATENATE" function. The output value you will get by using ampersand function will be the same as the one you will get by using CONCATENATE function. For example, =A1 & B1 returns the same value as=CONCATENATE(A1, B1)

To know more about the CONCATENATE/ ampersand function, you can refer to the following webpages:

http://office.microsoft.com/en-in/excel-help/concatenate-function-HP010342288.aspx

(**Important:** Below mentioned link is of a third-party website. We recommend you to update your security software thoroughly before clicking on the link.)

http://excelsemipro.com/2010/08/concatenate-function-or-ampersand-operator-in-excel/

(**Important:** Below mentioned link applies to Excel 2007. Still, you can refer it to understand more about ampersand function.)

http://office.microsoft.com/en-in/excel-help/combine-the-contents-of-multiple-cells-HA010248390.aspx?CTT=1

GuruAid.com

To know more about the CONCATENATE/ ampersand function, you can refer to the following webpages:

http://office.microsoft.com/en-in/excel-help/concatenate-function-HP010342288.aspx

(

http://excelsemipro.com/2010/08/concatenate-function-or-ampersand-operator-in-excel/

(

http://office.microsoft.com/en-in/excel-help/combine-the-contents-of-multiple-cells-HA010248390.aspx?CTT=1

GuruAid.com

Jul 30, 2014 | Microsoft Excel 2010

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

Try using a formula like this in Col AI:

=IF(AG9<>1,0,MAX(AI$1:AI8)+1)

A couple of notes:

1. You'll need to adjust the references for this formula in the first row of each new column you use.

2. If you don't want Zeros for the cells that don't increment, the you can use "" instead to get blanks.

=IF(AG9<>1,0,MAX(AI$1:AI8)+1)

A couple of notes:

1. You'll need to adjust the references for this formula in the first row of each new column you use.

2. If you don't want Zeros for the cells that don't increment, the you can use "" instead to get blanks.

Apr 25, 2014 | Microsoft Excel for PC

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

best shot that can attempt, hope it helps

Nested Classes vs. Inner Classes

A*nested* class is a class whose definition appears inside the definition of another class, as if it were a member of the other class. For example, if a program contains class A {
class B {
// fields, methods of class B...
}
// fields, methods of class A...
}
then class B is a nested class of class A. Code outside of the methods of class A can refer to class B by calling it A.B, using the same dot notation as for field and method references. Within the methods of class A class B can be used without qualifying the name. B could be hidden from code outside of class A by declaring it private, just as with fields and methods.

A nested class like B is known as an*inner* class. An inner class has access to the fields of an *instance* of its enclosing class. For this reason, an instance of B can only be created in association with an instance of A, using the expression
/it instanceA.new A.B(...)
outside of A's methods, where *instanceA* is an instance of A, and

new B(...) inside of A's methods. The new instance of B also knows about the enclosing instance of A, and can refer to it using the expression

A.this We can think of an instance of an inner class as having two this pointers, one for itself and one for its enclosing instance. An inner class may be nested within another inner class, so an inner class can even have multiple levels of this pointers. A nesting depth more than one level is quite uncommon, however, and should usually be avoided.

A nested class can be declared static, in which case it has reduced access to its enclosing class. For example, if B were declared static above, it could no longer access the instance variables of A, and there would be no associated instance A.this. Static nested classes are known as*nested top-level* classes, because they are exactly like classes declared outside any other class, except for the way they are named. Instances of a static nested class are created using regular new, as in
new A.B(...)

Nested Classes vs. Inner Classes

A

A nested class like B is known as an

new B(...) inside of A's methods. The new instance of B also knows about the enclosing instance of A, and can refer to it using the expression

A.this We can think of an instance of an inner class as having two this pointers, one for itself and one for its enclosing instance. An inner class may be nested within another inner class, so an inner class can even have multiple levels of this pointers. A nesting depth more than one level is quite uncommon, however, and should usually be avoided.

A nested class can be declared static, in which case it has reduced access to its enclosing class. For example, if B were declared static above, it could no longer access the instance variables of A, and there would be no associated instance A.this. Static nested classes are known as

Jun 09, 2010 | Sun Java Programming Language (cdj-275)

- Start Microsoft Excel.
- Press ALT+F11 to start the Visual Basic Editor.
- On the
**Insert**menu, click**Module**. - Type the following code into the module sheet. Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert cents and set MyNumber to dollar amount. If DecimalPlace > 0 Then Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumber = Dollars & Cents End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19... Select Case Val(TensText) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function

- Select the cell that you want.
- Click Paste Function on the Standard toolbar.
- Under Function category, click User Defined.
- Under Function name, click SpellNumber, and then click OK.
- Enter the number or cell reference that you want, and then click OK.

- Select the cell that you want.
- Click Insert Function on the Standard toolbar.
- Under Or select a category, click User Defined.
- In the Select a function list, click SpellNumber, and then click OK.
- Enter the number or cell reference that you want, and then click OK.

Apr 01, 2010 | Microsoft Windows XP Professional

why not? however, you can also insert an apostrophe (') at the start of the equation before copying the entire formula so that the formula will be treated as a text thus preserving all cell references. dont forget to remove the apostrophes after you have pasted them though for the formulas to work again.

Jul 29, 2009 | Computers & Internet

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

The best solution may be to re-define the named columns, or to add new names to multi-column arrays, then use the VLOOKUP function.

Note that the "indexed" named cells must be sorted by the index column.

Test (this is a 2 year old question)... Please ignore ths following. The plus symbol does not display.

Upper key plus symbol displays:

Right plus symbol on number pad displays:

[Shift] Right plus symbol on number pad displays:

[Alt] [4] [3] displays:

slash/slash/plus displays: //

slash/plus/slash displays: / /

Plus Plus Plus displays:

upper Plus Plus Plus displays:

Shift Right plus plus plus displays:

end of plus test.. thanks.

Note that the "indexed" named cells must be sorted by the index column.

Test (this is a 2 year old question)... Please ignore ths following. The plus symbol does not display.

Upper key plus symbol displays:

Right plus symbol on number pad displays:

[Shift] Right plus symbol on number pad displays:

[Alt] [4] [3] displays:

slash/slash/plus displays: //

slash/plus/slash displays: / /

Plus Plus Plus displays:

upper Plus Plus Plus displays:

Shift Right plus plus plus displays:

end of plus test.. thanks.

Dec 09, 2008 | Microsoft Excel for PC

Assuming that all of your data is in a single row number 4 and between columns N and PF

Try:

{=OFFSET(N4,0,MATCH(TODAY(),N4:PF4,0)+1,1,1)}

The MATCH function looks up the value of today() in the range N4 to PF4 and returns the number of columns offset from the beginning of the range. (The 0 here does an exact match)

The OFFSET function returns a value from a cell a specified number of columns from a reference cell, in this case N4, which is the first column that contains the search data. We need to add on to this value to skip the Interest column.

Regards,

Daryl

Try:

{=OFFSET(N4,0,MATCH(TODAY(),N4:PF4,0)+1,1,1)}

The MATCH function looks up the value of today() in the range N4 to PF4 and returns the number of columns offset from the beginning of the range. (The 0 here does an exact match)

The OFFSET function returns a value from a cell a specified number of columns from a reference cell, in this case N4, which is the first column that contains the search data. We need to add on to this value to skip the Interest column.

Regards,

Daryl

Jan 25, 2008 | Computers & Internet

Nov 18, 2017 | Computers & Internet

Nov 18, 2017 | MAC Computers & Internet

111 people viewed this question

Usually answered in minutes!

Tell me how the numbers 1 to 133, 134 to 266, wtc relate to the 10000 staples and what the results (0, .34, .67, 1, 1.34, etc.) are percentages of (a case?) and I will see if a VLOOKUP or HLOOKUP formula will work.

jmorganti

×