| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Formula Help: When a Number is Reached
I need a formula that will reference column A when column B has reached/eclipsed a given number.
Example: When column B range reaches or surpasses "30.00", tell me the corresponding value in column A The expected, and correct, result of the formula should be "4" Data Column A - Column B 1 - 25.05 2 - 27.64481038 3 - 29.99606543 4 - 32.1630163 5 - 34.18397103 6 - 36.08544701 7 - 37.88672718 8 - 39.60236759 9 - 41.2436836 10 - 42.8196825 I need a formula that will reference column A when column B has reached/eclipsed a given number. Example: When column B range reaches or surpasses "30.00", tell me the corresponding value in column A The expected, and correct, result of the formula should be "4" Thanks |
|
#2
|
|||
|
|||
|
Quote:
I suppose that it's possible to you to use column C or some other column and write formulas there. COLUMN A contain numbers or anything else COLUMN B contains those values that you want to check out COLUMN C at C1 is following formula =IF(B1=$D$1;A1;"") Copy this formula down so far as you have numbers at column B COLUMN D cell D1 contains the search value. At your exsample number 30 COLUMN E at cell E1 contains following formula =MIN(C:C) according your exsample, this should show number 4 as a result of formula. Now you have the situation you described and at column C you can see values only if the corresponding number at column B is bigger than value in cell $D$1. Cell E1 shows the smallest number at column C, which is at the same time, the first one which is shown if you scroll down the worksheet. So, give the number to the cell D1 and you will see which number at column A corresponds the value you put to D1. This result comes to cell E1 *** |
|
#3
|
|||
|
|||
|
If it was me I would just run a macro to loop through column "B" as you listed. In the example I submitted it kicks up a message box telling you what the corresponding number value is when it goes above 35.
Code:
Sub FindValue()
Dim rcount As Integer
Dim a As Integer
'gets a count of how many rows there are in column B
rcount = Range("B" & Rows.Count).End(xlUp).Row
'loops through looking for a value greater than 35 in column B
For a = 1 To rcount
If Range("B" & a) 35 Then
MsgBox (Range("A" & a))
Exit Sub
Else
End If
Next a
End Sub
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of cells until blank cell is reached | Excel Worksheet Functions | |||
| Inserting the same value until the same value is reached again | Excel Discussion (Misc queries) | |||
| Add one until 171 is reached | Excel Worksheet Functions | |||
| Some formula to add results until a given value is reached? | Excel Worksheet Functions | |||
| Adding a certain number until minimum is reached | Excel Discussion (Misc queries) | |||
Linear Mode
