Return Range Of Numerical Values In Single Column Based On Frequency Percentage

Oct 25, 2005

I have a single column of numerical values that may repeat many times within
the column.

I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value
NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.

View 9 Replies


ADVERTISEMENT

Return Result Based On Percentage Being Within A Range

Jan 17, 2008

I'm attempting to do the following:

A1 contains names of projects.

B1 contains variances from budgets, being positive or negative numbers.

C1 will contain results.

I need to know which of the B1 cells contain a number equal to or greater than 20%/-20% and output the names in A1 that match the criteria to C3.

View 8 Replies View Related

Return Nth Value From Single Column Range

Apr 29, 2008

Assume A1:A10 filled with the characters A to J.

I'm looking for a way to return the letter "C" when 3 serves as the index_num BUT I do not want to present all 10 characters in the formula.

I would like to use the range A1:A10 =Choose(3,A1:A10) or, if applicable, to name the range as DATA and then use: =Choose(3,DATA)

I tried all I know, including INDIRECT - but found no solution [is there any ?]

As I'm familiar with all workarounds - I will appreciate replies that use CHOOSE.

View 9 Replies View Related

Single Cell Two Numerical Values - Multiply In Different Columns

Jun 27, 2014

I am looking for formula , single cell two numerical values , multiply in different columns

example :

Cell (A1) contain: =350,23+25,59 (two values in a single cell)

request:

in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number

View 2 Replies View Related

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 Replies View Related

Find Text In Column - Return Numerical Value

Dec 15, 2011

I need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.

View 2 Replies View Related

Getting Function That Will Return Numerical Value Based On Dropdown List

Aug 3, 2014

What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.

I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.

View 4 Replies View Related

Function To Sort A Range By The Values In A Specific Column And Return The Range

Mar 25, 2009

I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.

My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer

For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function

View 9 Replies View Related

Excel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value

Jul 2, 2012

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001
q9002
q9003
q9004
q9005
q9006
etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

(Using Excel 2010)

View 7 Replies View Related

Count Frequency Of Continuous Occurrence Of Text Value In Range Of Values

Dec 9, 2013

In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this

sat
sun
mon
tue
wed
thus
fri

[Code] ..........

In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.

View 4 Replies View Related

Formula To Return A Values Based On Whether A1 Is Within A Range?

Aug 5, 2014

I am trying to create a formula that will do the following:

If A1 is between 95 and 99 then populate with 2

If A1 is between 90 and 94 then populate with 3

If A1 is between 85 and 89 then populate with 4

If A1 is between 80 and 84 then populate with 5

If A1 is below 80 then populate with 6

View 11 Replies View Related

Return Values Based On The Range In Another Cell

Nov 28, 2012

I am trying to put together a calculation sheet for court costs and I need a cell to return a value based on the amount entered in another cell. For example, if I input a value between 1 and 5 in cell A1, I would like "Cat" to appear in cell A2. However, if I input a value between 6 and 10 in cell A1, I would like "Dog" to appear in cell A2. Is this something that can be done without VB?

For the real spreadsheet, I would need the following outputs in cell A2 based on the respective ranges in cell A1:

Output to A2 = $405 if input to A1 is < $50,000.
Output to A2 = $905 if input to A1 is ≥ $50,000 and < $250,000
Output to A2 = $1,905 if input to A1 is ≥ $250,000

View 3 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

View 9 Replies View Related

Return Column Values Based On Criteria?

Jan 31, 2014

I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45

The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.

I need a formula that would look at my source data, and populate a sheet in the following layout

agent_id, agent_name, title, start_time, end_time

The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30

If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.

View 2 Replies View Related

Avoiding Non-numerical Values In A Range

Jan 1, 2009

I have a range, A1:A10 that I want to sum, min, and max. I want to ignore any text or #DIV/0! values in this range. I know how write the formulas except how to ignore the text and cell errors. Can someone steer me in the right direction?

View 5 Replies View Related

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.

I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.

View 2 Replies View Related

Return Values In Range Based Upon Relationship To Maximum Value

Jan 22, 2008

I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.

The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.

View 5 Replies View Related

Return Percentage Of Number Based On Its Value

Sep 8, 2007

Spreedsheet on on sales and taxes

Need formula for cells.

3 different cells involved... B1 will have $ amounts, B10 tax code, B25 will have total of tax applied due to code.

Exp; cell B10 will contain the tax code... if you enter..... "1" in the cell the calculation will be 7% in cell B25, enter "2" in cell B10 and the calculation will be 6% in cell B25 , and if you enter "3" in cell B10 the calculation will be 13% in cell B25.

View 6 Replies View Related

Summary Of Numerical Values, Based On Several Conditions

Oct 16, 2008

I am trying to do is a summary of numerical values, based on several conditions.

column a column b column c
local loop live cross connect
loop A cancel 100
long haul live 10000
long haul pend canc 1000
loop Z live 100
x-conn live cross connect


In column a, i have several choices and i want to focus on those choices that fall into column B "live" or "pend canc" and then only have numerical data in column C

I've tried several different formulas like

=SUMPRODUCT((a30:a38="Loop A")*(a30:a38="Long Haul")+(b30:b38="Live"),cX30:cX38)

I know the result of this should be 111000 but it's not working...I usually get n/a. I know about using cntl-shift-enter to use the formula properly.

I can get one variable to sum up properly using =SUMIF(M9:M21,"Live",BW9:BW21)

but, that leaves out anything that is pend-canc

I have also tried =SUM(IF((a9:a21="long haul")*(b9:b21="live"),cX9:cX21)) which gives back a value that is accurate.....

View 8 Replies View Related

Assign Numerical Values By Letter Across A Range

Dec 17, 2007

I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4.

=SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4)

This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0.

Example:

J2...K2...L2...M2...N2...O2...P2...Q2...R2...
A....B.....B.....C.....B.....D....B.....C.....E

the output of the above sequence should be

1+1+1+2+4+4+0+4+4+0 = 21

View 4 Replies View Related

Frequency Formula To Show Final / Total Numeric Values Appears In Adjacent Column

Apr 29, 2013

Frequency

Formula to show the final/total a numeric values appears in an adjacent column.

I am currently attempting (I've researched many posts on this), to count /show the final totals a duplicate numeric value appears in one column, in an adjacent column (example below).

I am currently using an array Frequency formula below (courtesy of the board) filled down in column Q. this works well to count the frequency of duplicate numeric values appear on the list.

Formula used in Column Q below
{=IF(C2>=0.1,MAX(FREQUENCY(IF(C2:$C$1000=C2,ROW(C4:$C$1000)),IF(C2:$C$1000C4,ROW(C2:$C$1000),""))))}

However my aim is also to show the final entry of a numeric value in adjacent column R, by displaying text to indicate this. My example below shows “final” in column R

NB: My list is sorted by column C to ensure all numeric values are in ascending order.

Example
Column C Column QColumn R
12567 1 final
15789 1
15789 2
15789 3 final
23456 1
23456 2 final
12678 1 final
18965 1
18965 2 final

View 5 Replies View Related

Data Validation Formula For Set Of Numerical And Text Based Values

Apr 30, 2014

I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:

I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)

And I would need the data validation tool not to include blank cells.

View 1 Replies View Related

Dynamic Named Range - Select Only Cells With Numerical Values

Jul 21, 2006

Is anyone aware of some way to use the " dynamic named range approach" to only select the cells with numerical values in a column and name this range?

I've looked at the examples on this site but can't find any solution to this particular problem although I have a feeling that this should be possible.

View 9 Replies View Related

Arrange Numerical Values In Order From Lowest To Highest In Column

Jun 19, 2014

Trying to arrange numerical values in order from lowest to highest in a column i have a code to do that but i need it to be able to but to a range in the column so it only sorts the values in rows 3 - 60 not all of the rows in the column

Sub Testsort()
Range("A3").CurrentRegion.Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

View 9 Replies View Related

Copy/Paste Rows By Numerical Range In Column

May 8, 2008

I would like it to scan an entire column (column E) for rows of data that fall into a range of numbers chosen by the user (like maybe a list box or combo box in sheet 1) for example a number greater than or equal to 8 but less than or equal to 15 and copy all of the rows and columns of data across the entire sheet that fit the range from sheet 1 to sheet 2. I would like to use a separate list or combo box for the min and max values in which to search as seen in the example sheet 1 attached. I have tried the autofilters, custom filters and macro recorder with no success as it seems the filters do not like ranges of numbers.

View 3 Replies View Related

Excel 2010 :: Conditional Formatting - Highlight Values Based On 2 Columns Of Numerical Data

Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:
6
6
14

Column L:
3
NA
17

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies View Related

How To Return Multiple Values From Array In Single Cell

Jul 2, 2014

I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.

I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:

To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).

I already have it working for returning a single value with the following formula but need it to return multiple values.

=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")

View 1 Replies View Related

How To Return Multiple Values From Array In Single Cell

Jul 2, 2014

I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.

I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:

To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).

I already have it working for returning a single value with the following formula but need it to return multiple values.

=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,
MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP
(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")

View 1 Replies View Related

Lookup Single Value In One Array, And Return Multiple Values

May 30, 2006

I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.

View 9 Replies View Related

Return Multiple Values From Lookup To Single Cell

Feb 20, 2007

i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved