Compare Highest Value With Cell Above & Calculate
Aug 4, 2007
I have a set of values in the 2nd column (Col B). I have shown in Column C (I've added some notes to the right).
mon........7..........(first value, so nothing here)
tue........8..........na, or ERR.......................... 8 IS the highest value in B, so no math to do yet
wed.......10..........na, or ERR.......................... 10 is greater than 8…the new highest value in Col B
thu.........8...........(20.00)............................. The value, 8, is 20% less than the Highest value so far in Col B
fri..........6..........(40.00)............................. The value, 6, is 40% less than the Highest value so far in Col B.....
Namely, any value in Col B that is LESS than the highest, greatest, largest value that has preceeded it in chronological order will reflect by what % it is lower. (Eg, 8 is 20% less than 10). When a NEW higher value appears in Col B, there would be no answer in C (or simply "ERROR", or n.a.), and this new, largest value would be what subsequent values in Col B are compared to.
View 4 Replies
ADVERTISEMENT
Aug 28, 2007
I'm having trouble with the "sister" formula I need to add. I've tried the function SMALL, but apparently not in the right format.... Here is some data again, with the answers I'm seeking & notes in Col C:
mon....6....(first data pt, so nothing here)
tue.....8.....33.33 (this is 2/6ths higher than 6)
wed...10....66.66 (this is 4/6th higher than 6)
thurs...8....33.33 (same as tuesday)
fri.......6.....00.00 (Equal to the lowest data pt, ie 6)
sat.....5.....(finally a vlaue less than 6, so: "ERR")
sun.....7.....40.00 (7 is 2/5th higher than 5)
mon....15....200.00 (10/5ths higher than 5)
tue.....4......(a new lowest value; "ERR")
wed....8......100.00 (4/4ths greater than 4)
That is, any value in Col B that is GREATER than the lowest, SMALLEST value that has preceeded it - in chronological order - will reflect the % by which it is greater. When a new lowest value appears in Col B, a simple text "ERR" would appear, and THAT new low value will be the new "target" the subsequent values in B are compared to. (In the example, once 5 appears, we stop looking at 6...)
View 5 Replies
View Related
Sep 6, 2007
Assume Cell A2 + A3 as the fraction: 60/100. How can I get the smallest fraction (3/5 in cells B2+B3). I do not want to use the build in cell format for fractions)
There are a few samples of want I would like to get a s the result (Red Digits). I will appreciate 2 ways: via VBA Code and via Sheet Build-In Functions.
View 5 Replies
View Related
Feb 1, 2013
Example.xlsI have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate in excel the highest 5 consecutive year average salary for each (if they have less than 5 years with salary, then it should just average the years the do have). Some people have breaks in service (for five years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
View 3 Replies
View Related
Jul 24, 2007
I am trying to compare two types of conditions, one that has 3 variables and the other that has 8 variables (each variable has a numeric range), which places the correct result in F6 and F7 of the atatched spreadsheet.
View 5 Replies
View Related
Apr 16, 2007
I want to compare cells and use a range of values to calculate totals (i.e. count if cell A1=boy and cell A2 equals either "fat" or "skinny")? Also, I tried using wildcard character "*" in the SUMPRODUCT formula, which didn't work (i.e. if cell A1="boy*" would return at TRUE if the cell contained "boy", "boys", "boy and girl", etc.). Is there a way to include wild card characters in a SUMPRODUCT formula like the ones described above?
View 9 Replies
View Related
Nov 27, 2013
I'm attempting to put together a script which in simple terms finds the 'Highest and Second' highest activity and associated FTE for a given sub group.
To be more specific:
On the "Slide 1" sheet look at the values in column J starting at row 7 until blank;For each value, then search the "All Monthly Direct Activities" sheet in column C for the same value, excluding the 'Subtotal' rows;Where a match is found, compare all the values in column E for that sub group;And Find the highest and second highest figure;
Once these are found copy this figure and associated value in column B and paste onto the "slide 1" sheet.
I'm not particularly well versed in writing VB, but I've been working with the code to see if I can get this work, which I have been unable to do.
I have attached a file which may perhaps provide a greater detail than my description of the problem containing the 'Source' and 'Destination' sheets.
View 9 Replies
View Related
Jan 15, 2008
If I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?
View 9 Replies
View Related
Feb 25, 2007
I have a list of names in one column and in the next column I have a list of numbers. The names and numbers go together. In anohter cell I would like to extract the highest number and the name related to it. The list changes often.
I would like to be able to put a heading saying that "name" (in anohter cell) made the highest score and the name is entered into a cell automatically.
View 9 Replies
View Related
Jan 28, 2013
I have a sheet, along the top is the Months of the year, to the side I have a list of products. At the bottom of each month in a cell I want to display the name of the product that has sold the most for that moth, is this possible:
I can display the highest number i.e Highest item sold in January was 108, but I want it to write the product name.
View 3 Replies
View Related
Jul 15, 2008
I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.
--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.
--Is there a function/filter that would be able to report the highest number recorded?
View 10 Replies
View Related
Dec 30, 2012
I have % figures in the following cells, E20, G20, I20, K20, L20, F24, H24, J24, G28, I28, H32, F16, J16, G12, I12, H8.
These figures are the result of a formula of other cells, with constantly changing values, therefore the % figures are constantly changing as well.
What I would like to do is highlight the cell with highest % figure, ideally to 'flash', either cell colour or flashing text.
View 7 Replies
View Related
May 4, 2006
I have a whole number value in cell F18. In cell F24 I want to enter a formula that allows me to divide the value in cell F18 by 4 and if the result is not a whole number, round it up to the next whole number.
View 2 Replies
View Related
Dec 22, 2005
I have 7 rows in the same column of type percentage. I'd like to turn
the text of the highest valued cell red. How exactly is that done with
contional formatting (vs. IF statements for each cell)?
View 10 Replies
View Related
Mar 2, 2012
pictorial representation find highest in the cell
data & output
View 1 Replies
View Related
Aug 18, 2014
I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.
What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.
For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.
So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.
View 7 Replies
View Related
Apr 15, 2009
Cells L5 - L65 have percentages. B5 - B65 Have the associates names. I want to write something in another cell that looks for the highest percentage and then prints the name as the highest.
View 11 Replies
View Related
May 10, 2014
I have a cell containing alpha and numeric. I would like to extract the highest number in the cell
the cell looks like this
the answer I would like is
====G99876554321000
9
T1111111100000000
1
I am using Excel 2010
View 3 Replies
View Related
Feb 21, 2014
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
View 3 Replies
View Related
Aug 29, 2012
I have two worksheets.
Column A on both sheets have an ID number which is unique to clients. Sheet one will only have a client ID once but sheet two may have the same ID multiple times
I need to compare both sheets Column A and if it finds a match, then paste the cell value on column E sheet two, into column K on sheet 1.
sheet 1:
A ..........................................K
Client ID............................. name
Sheet 2:
A ...........................................E
Client ID............................. name
View 3 Replies
View Related
Jul 29, 2009
if I have a range, say 33-35, I want to put a value into a cell and compare it to that range. It will set another cell to a certain value, based on the range.
i.e. if (35 > A1 > 33)
A2 = B
elseif (37 > A1 > 35)
A2 = X
View 3 Replies
View Related
Oct 4, 2009
I'd like to compare two cells, and if the first cell is either >150, <161, OR =165 AND the second cell is ="E" to output a 1, and if false, output a zero.
I have =IF(AND(AND(Sheet1!B3>150, Sheet1!B3<161, Sheet1!B3=165 ),Sheet1!E2="P"),"1","0")
But i know that is incorrect because Sheet1!B3 can not be greater than 150 AND less than 161 AND = 165. I can't figure out how to make it >150 AND < 161 OR = 161.
View 14 Replies
View Related
Apr 2, 2012
Here is what I want to do:
If AND(NOT(ISBLANK(A1);A10) then B1= "NOT OK"
else
If AND(NOT(ISBLANK(A1);A1=0) then B1="OK"
End if
How to do this for a range A1:A10 in vba??
View 5 Replies
View Related
Apr 11, 2012
I have a report that contains 76 columns and any number of rows. I have seen between 100 and 23,500 rows. The data in each cell of each column must contain a precise number of characters. What I am trying to do is create a macro that will LEN() each cell and compare the value to row 1 of the cell's column. Row 1 contains the value of the required number of characters, row 2 contains headers and the data starts on row 3.
This is what I have so far:
Sub LENtorow1()
Dim cel As Range
Dim row As Integer
[Code] .....
It is this portion that is causing me troubles: Range(cel(ActiveCell.Column & row)). If I use a static value it works fine but when I try to make it recognize the first row of the current cell's column there is a break down. I have tried several variations, the one listed here is my latest attempt.
View 2 Replies
View Related
Aug 2, 2012
I am wondering if you can write me a VBA code for comparing the value inside a cell.
Code:
For i = 1 To 159
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) then
..............
Else
[Code]....
View 9 Replies
View Related
Mar 14, 2013
I have a spreadsheet and everyday it would change in length.
But I would like a macro that compares the value of each cell in column B against the value in corresponding cell in column D then moves to the next roll - so eg B2 against D2,B3 against D3......B50 against D50 until all rows are compared.
And when it finds a different makes the background colours go red !
I have a bit of a code but I think I need to loop to compare all populated cells !
View 4 Replies
View Related
Mar 26, 2013
I have data all in one column that I need to compare.It's basically the data between the 2nd and 3rd commas being compared to the data after the 2nd colon. I have over 860,000 entries in the database that this data is extracted from so I can't go through it one-by-one.
12EDM1,1,1638413100250,Match_Location_Number:1:1638413100250
VIK1,1,1638413100251,Match_Location_Number:1:1638413100251
WHI1,1,1638413100252,Match_Location_Number:1:1638413100252
HPR1,1,1638413100253,Match_Location_Number:1:1638413100253
View 9 Replies
View Related
Oct 23, 2009
I have a column of dates formated DD/MM/YYYY in column H in one sheet. I need a formula that if a cell in H is more less than 30 days from today's date to display the value from an adjacent cell.
example:
Data source table *Serial #ID #TempExp date9384-63838QDYHEISAV4
YES01/18/201010384-78618PDQA7O7FUO
yes11/18/200911384-78708QKM5XA12BK
yes12/25/2009 Excel tables to the web >> Excel Jeanie HTML 4
I need a formula that will, if the exp date is less than 30 days from today, place the serial # in a cell on a different sheet.
View 10 Replies
View Related
Jan 23, 2007
I am trying to create VBA function that will compare search a worksheet row by row for a value in column c. If column c matches any of the array values, i would like the cell value from column d to be entered in another array. I'm familiar with adding values to an array, but i'm having trouble with the code to compare the values. the array is a string and the cell values are text.
View 3 Replies
View Related
Nov 30, 2007
i am attempting to recreate a 1 armed bandit in excel and was wonderin the following.
Is it possible for excel to look at a cell and determine how many of a certain character there are within it?
i.e. if a cell contains 112 can excel work out that there are two 1s and one 2.
I am hoping i can get excel to determine whether the random output is a jackpot or not i.e 3 of the same number.
View 9 Replies
View Related
