Formula To Find Value In Between Two Values

Apr 29, 2014

Bonus calculation model...

100% of sales target = 100% of bonus
110% of sales target = 125% of bonus
120% of sales target = 165% of bonus

etc...

How can I solve for someone who books 107% of sales target?

View 4 Replies


ADVERTISEMENT

Formula To Find The Sum Of Values That Were NOT Equal To My Quoted Values

Aug 21, 2008

Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!

Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))

Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))

View 9 Replies View Related

Formula- Find Values

Apr 7, 2009

I have a series of worksheets that have materials displayed as shown below:

Column A Column B Column C
Material A

material B
Material V
Material F
Total Price 123456

Material C

material B
Material V
Material G
Promo Price 123456

I would like to know if there is a formula can perform a lookup based upon the material in column A and then return the Total price in column C. For each material in column A there is a price but only 1 total price for each grouping.

View 9 Replies View Related

Formula To Find And Sum Values In Table

May 3, 2013

I'm trying to come up with a formula to find and proper sum the value in a table like this one:

Month/year
1995
1996
1997
1998

Jan
3,63%
0,86%
3,5%
1,5%

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

But the actual table goes till december and the years till 2013. For instance, if I need the cumulative index from, let's say, jan/2010 to feb/2013, I'm having trouble with this formula. I can't get excel to sum them properly.

View 1 Replies View Related

Formula To Find Multiple Values And Return Value

Mar 24, 2014

Column A:

pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....

I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:

Column A: Column B:

pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white

I have different product feeds (csv) and i will like to search for colors in the product names.

View 2 Replies View Related

Formula To Find Matching Values In One Column And Return Totals For All Rows That Match

Dec 26, 2013

table1.jpg

I am looking for a formula that will satisfy the following:

1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16

View 3 Replies View Related

Using Vlookup Or Similar Function To Find Missing Values Compared To Base Values

Apr 30, 2014

I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.

For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.

Sample:

John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional

View 1 Replies View Related

Find Same Values In Different And Unequal Cell Ranges And Refer To Values

Jul 2, 2009

I have data similar as shown in the following:

A1A11
B2AA1122
C3B22
D4C33
D44

The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this

A111
AA1122
B222
C333
D444

the third column always will have at least the same letters as the first column, but new letters/entries can occur.

View 3 Replies View Related

Compare List Of Values In Order To Find The Common Values

Feb 11, 2013

compare some list of values in order to fiind the common values.

View 1 Replies View Related

Macro: Find Duplicate Values & Sum Values. Per Day Basis

Oct 13, 2006

I have data that looks like this:

day# id amount
1 56575 0
1 56675 0
1 56680 0
1 56683 0
1 56681 0
1 51810 0..............

How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....

View 2 Replies View Related

Find Formula (find Jobs With Split Names)

Aug 5, 2009

DSee the attached.

The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.

BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).

I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.

View 5 Replies View Related

Find The Formula That Is Used To Find These Numbers

Dec 26, 2009

The first 7 digit number is the input to a certain equation. This equation outputs the 7 digit number after the space.

Input (7146127) ==> Equation ( Unknown) ==>Output (2871082)

These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.

71461272871082
53820056426572
61070709150530
75833373805331
55466908456008
49264716615511
12387558353260
20013928484175
67975657084961
38006283229553
68973249414920
65179634653276
66971872613128
59646203411273
69785611400836
58284602307740
64723009925464
57930863495088
58816593932906
62736335606616
52119154778580
60043359437317
61921553996320
51507747216899
13197956063818
64210347411256
65399671193442
55656585589500
55148886441637
30850639587521
69081133766387
54139417804940
53408393698534
62310293191095
30512556590168
14514168265124
89078208134056
85177364363577
68714858216023
17584065849687
52691521917548
9840524785846
59268687494601
89374963791119
45989738961270
65040033345415
15061518946940
55392084949214
45103824265242
84181944909073

View 9 Replies View Related

Formula For Obtaining List Of Unique Values But Not Including Values Which Contain IC

Aug 14, 2014

1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).

I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)

2. To include all unique string values except those starting with the letters "IC"

3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.

Here is the formula I currently use which serves #1 (above):

[Code] .....

Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.

Example of desired results:

Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |

View 14 Replies View Related

Sorting Values: Find Points With Similar "y" Values

May 5, 2008

I have x coordinates in column 1 with coresponding y coordinates in column 2. From my data of x,y coordinates I want to find points with similar y values. In my data I have defined groups of numbers, i.e. I have a set of numbers with y values around 30 (+-10), then a new group with y values around 60 (+-10), and so on... Sometimes the groups are not totaly separeted, there could be a few points with y values between 30 and 60. These points can be grouped with the closest group of coordinates (30 or 60). Each group of x,y coordinates could be copypasted in the columns to the right (columns 3 and up).

So what I want to do is find a group of coordinates. This group will have at least 40 individual points +-10 from a group mean. The coordinates need to be sorted out from the data and put in seperate columns.

View 9 Replies View Related

Formula For Getting Average Of Values Without Counting Zero Values In Range?

Dec 23, 2011

Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.

=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")

View 9 Replies View Related

Find Values For Key

Oct 6, 2009

I have table values like below in sheet

Key values
ABCD
ACDE
BFGH
CIJK
CJKL
CKLM
BGHI

If i enter Value B in one of cell, it should pick values of FGH, GHI. Can this be done using formula or other methods??

View 2 Replies View Related

Find Containing Values

Feb 18, 2009

In Excel 2007 can you do a find that contains multiple valued cells? I know you can find one at a time but it takes to long.

Example.

column A

5963190,7281143,7283188
4559000
7286000
7289696
5963499,5962468
5881017,7281176,4556060

That is my list to search in.

Here is the list to find that may contain these values.

7289696
2419888
9741452
7283188
7281176
4997755

If either match I need them to Highlight a different color.

This will not work under the duplicate format because it will not pick out what may contain it will on pick exact.

View 9 Replies View Related

Find Values Corresponding To First And Last Day Of A Month?

Jan 28, 2013

I am looking for a smart way to extract the values corresponding to the first and last day of a month I specify from my time series. Column B has the dates (dd/mm/yy), column A is text(B,("mmm-yy") and column E has the values I need to extract. I have a column with all the months of the year: Dec 12, Jan 13, Feb 13 etc... and I need to pick the value in E that corresponds to the last observable day of each month and divide that by the value corresponding to the first observable day of the month.

[URL]

View 5 Replies View Related

Find Cells That Sum Up To Given Values

Sep 10, 2006

I have a column (i.e. A) with hundreds of numbers. I'd like to be able to write a number in a cell (i.e. B4), and have Excel find and tell the coordinates of those cells in column A that sum up to the result I wrote in B4. I also would like to be able to "hit a key" and see the next possible set of result-cells.

View 7 Replies View Related

Find Two Values In One Column?

Mar 11, 2014

I've got two question on how to 'find' some values in a specific data file, namely:

- the '52-Week High' of the share price (highest value of the share in that specific year)
- the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

Simplified, my data looks as follows:

-In column 'A' I've a created a line of unique edentifiers (company CUSIP + year -> combined column D and E), in my set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows
-In column 'B' the date is shown -> its daily trading data
-In column 'C' the daily share price is shown
-In column 'D' the company's CUSIP (edentifier for specific company)
-In column 'E' the year

Sheet 1:
1.JPG

My final data set has to look something like this:
-In column 'A' the unique edentifiers (company CUSIP + year)
-In column 'B' the specific year
-In column 'C' the '52-Week High' of the share price (highest value of the share in that specific year) -> red numbers from sheet 1
-In column 'D' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..) -> green numbers from sheet 1
-> off course all the empty cells in column C and D normally contain the missing values

Sheet 2:
2.JPG

The big question is how do I manage to identify the red and green colored values (and link them to 'sheet 2')?

Regarding to the '52-Week High' (red colored in the sheets) I've already tried with the following functions:
- VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' this would become '12'
- Combined INDEX/MATCH: The same problem as with VLOOKUP
- IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 1: this would be '115'

Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.

View 5 Replies View Related

Find Values That Appear In Various Columns

Apr 18, 2014

I have 6 columns containing Agency IDs of different years. I would like to know which agencies appear in all the years. That is which agencies were funded for all the years. How can I do this? perhaps Vlookup?

View 11 Replies View Related

Find The Average Values

Oct 4, 2008

Column B: The day of the week that the date is, Sunday, Monday, etc.
Column C: Every date in 2008
Column D: my data (the number to be averaged)

What I need the formula to do:
Look in Column B for every instance of "Sunday", and count them up (they count only if the data in column D is not 0) and use that number to average the number in column D

So if I have 52 Sundays and we are only 4 weeks into the year and the 4 data points are 50, 100, 75, 25. The average will be 62.5 instead of 4.8 (divided by 52).

View 6 Replies View Related

Find Top Ten Values And Return Value From Another Row?

Nov 23, 2013

I have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).

=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table

This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.

=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))

Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table

In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.

Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013

[Code].....

View 3 Replies View Related

Find Part And Add Values

Apr 16, 2009

I don't know if this is even possible via VBA, but thought I'd throw it out there and see what you guys think.

In the attached workbook column D contains fields for the UsagePN. If a part is "rolled-up" in our order system to a new part number, then the OLD part number, (in colmn B), is associated with a UsagePN. What I need to do is, for every part that has a UsagePN in Column D, I need to find that part number in column B and add the inventory values from the old part number to the new one.

For Example:
Row 74 in the attachment has a UsagePN of AC1230105V. I need to take the values from cells G74 thru K74 andd ADD them to the existing values in G77 thru K77, which are the inventory values for AC1230105V.

In other words, since part number AC1230105 has rolled-up to the new part number AC1230105V, I need to add all of the inventory to the new part number, AC1230105V.

After this is done for ALL parts with a usage part number, I will delete all the rows containing UsagePN's.

Here's the catch, as you can see in the example, there is no consistency, so a simple formula like adding the values to the same part number with a "V" at the end won't work. I guess you would have to do something like for each row with a value in column D, dim that row so you can refer back to it, then search for a match in column B, insert a "helper" row below where the part number is found to add the quantities, then replace the original with the helper row, and then delete the helper. One of you guys probably knows a better way.

Keep in mind that you won't always find a match, so the macro needs to continue to the next part if no match is found.

I hope I was clear. This one looks like it will be a nightmare, but I hope somebody on here has some ideas.

View 14 Replies View Related

Find/Replace Values With X

Jun 8, 2009

I've got a piece of code I'm stuck on. Basically, I have a range of data and most cells are empty. However, I want to replace all the nonblank cells with an X. I can't quite figure out how to have the code replace something non-specific...

View 3 Replies View Related

Find String, Add Values

Aug 2, 2009

1) Find strings "CA", "Ca", "ca", "California", "california", "cali", "Cali" in Column F

2) If the string is found, then record the Real value at Column D of that row in Column I

3) Continue this until there are no more strings in Column F

4) Add the values recorded in Column I and display the final value at the bottom of the list.

View 2 Replies View Related

Find Values That Equal Sum

Dec 5, 2012

I have a spreadsheet that has a number of values and a separate sheet that has a paid total. What would be the easiest way to find the values in sheet 1 that total the sum in sheet 2? There may be more than one possible solution as to the items that total the sum.

View 1 Replies View Related

Find Last Empty Row And Sum Up All Values Above?

Dec 13, 2012

I am looking for a VBA code which will search for a last empty row in given range, and sumup all the values above it in the same cell...this has to be repeated for other columns as well. The column and row numbers may vary depending on another code. In any case the summation has to be in the last empty row...The excel table should look like as below, the last row is summation of all the values above..

A B C D
1 - - -
2 - - -
- 3 - -
- 4 - -
- - 5 -
- - 6 -
- - - 7
- - - 8
3 7 11 15

View 2 Replies View Related

Find Intersection From Known Values

Nov 20, 2013

I have store numbers (#3, #5, #6) (not consecutive, but ascending right to left) across the top, and dates (11/19/2013) ascending down the left.

I know date, I know store number. I need VBA code (excel 2000) to move cursor to the intersection of the two.

View 9 Replies View Related

Find The Lowest Values ...

Jan 19, 2007

i have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2 i want to do this with every row from row 2 to 55

View 9 Replies View Related







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