I have a spreadsheet in Excel 2003 with 16 columns of weather data.
I am analysing the data in the format MAX and MIN in each column.
I have the formula to find the highest (an lowest) figure in each column but what I need is a formula that can find the date for corresponding high (or low).
For example, if the highest humidity is in cell H509 I need to register the date in cell D509.
Can this be done?
Similar Excel Video Tutorials
Edit Recorded Macro
- See how to record a MACRO to copy data to a new location, and then edit the code. See the VBA functions RANGE and OFFSET.
Edit Recorded Mac ...
I have a set of some prices. Columns Headers are OPEN, HIGH, LOW & CLOSE.
The set has a 10 days of data. Now at the end of the last record one cell is having a price of Last 5 day's Highest High Value and another is having a cell value which is the Lowest Low of last 5 days.
If the last record is Lower than previous 5 day's Lowest Low than Cell Value will be Highest High of previous 5 days.
If the last record is Higher than previous 5 day's Highest High than Cell Value will be Lowest Low of previous 5 days.
I do not know whether it is possible to do it in a single cell. I tried but failed. Please guide & help.
Thanks & Regards
Last week I got some great help from the folks on the forum, and had my problem with times all ironed out. Until a co-worker threw a monkey wrench into the mix, and I'm hoping someone can help me with this latest wrinkle. Argh!
Here's my problem;
I'm a weather forecaster, using MSExcel to keep track of weather warnings, and I need help with a formula that will calculate the difference between when a weather warning was issued and when the weather phenomena actually occurred. Last week, when I sought help, I only had to deal with a difference of hours and minutes, and had my cells formatted as HH:MM. Cell B19 is the time I issued a high wind warning. Cell D19 is the time we actually saw the high winds. I was using the formula =D19-B19, and it worked fine.
But now that i have to throw in a date as well, this formula doesn't work-it returns the answer #VALUE!
I tried custom formatting my cells to DD/HH:MM, hoping this would fix the problem. It didn't.
Can anyone help me figure this one out? I need a formula to calculate the difference in days (if any) as well as the hours and minutes.
Thanks in advance for any help you can offer.
Hey desperate help needed!
On a spreadsheet i have a column with dates ranging from one date to the other e.g. 26/2/2006 = lowest 13/07/2007 = highest.
I need to find a way that I can arrange the dates from highest to lowest so i can see which ones are out of date.
I am using the following formula in a cell to get the High Price of EUR/USD for that day (forex, quotes from MT4) :
Now - on my spreadsheet. Users are required to enter the Highest price met for the duration of their trade AND the lowest Price met for the duration of their trade.
I have 2 problems:
The HIGH price quoted (using the formula above) is not necessarily going to be the Highest Price during (users) trade. Example, that high price might have been hit well before user even enters into a trade.
So - i have figured a workaround (i think) but just need a bit of help to implement this.
I need to write this in VB code.
Once a user enters into a Trade - i will need the following formula pasted
into column active Cell row + column BP. However - this formula needs to change depending on what the user selected in Active Cell row + Column C.
Example, JPY/USD or EUR/GBP or EUR/USD, etc, etc.
Since i need to grab the Highest & Lowest point reached during that particular trade. What VB code can i use to look at the cell's Highest Value reached (since this will always be changing) and then store that data and (also) for the cell's lowest value reached and then store that?
So now we have the entry point fixed because the formula for getting the live quotes ONLY gets entered into the cell once user enters into a trade.
However, once user ends this trade (which will be on Active Cell Row + column BO) i would then like the formula in Active Cell Row + Column BP to get overwritten with the stored data.
I hope all this is possible.
Hope someone can help.
Thanks in advance.
I am working on a spreadsheet for calculating energy savings. The biggest factor is weather data. I want to use the same worksheet regardless of where the project is located and I want to use weather data from a library of other excel files. I want to create a pull down menu with the list of cities that I have weather data for. Then i want to import the weather data for the city that is selected from the pull down menu but I can't figure out how to do it. Anyone have any ideas? I tried using the pull down menu to return the file name for that city and using that in an embedded formula but it doesn't work.
I have a workbook, with an api feed feeding data to it. e.g. cell A1 contain data of current weather: "81"
As the weather changes, increasing and decreasing, the number on the cell changes to denote the current weather.
Is there a way to conditional format the cell, so that if the weather increases the color of the cell is green and if the weather decreases the color of the cell is red?
In columns D:G, I have four different values. I am trying to write formulas in columns H:K, where column H would have the minimum value, column I would have the second lowest value, column J would have the third lowest value (or second highest), and column K has the maximum value.
With the MAX and MIN functions, I can easily get the lowest and highest values in the destination columns. How do I find the second lowest value and third lowest value? I was thinking about using the RANK function but I'm not sure if that will work - I thought you guys would know a quick way to do this.
OS = Win7
Excel = 2007
I am trying to sort 1611 rows of data, which is under 5 column headings, the data looks like this:
The sort needs to sort through the data and arrange them by LCHKIN from lowest date to highest, then from TOT CHCKOUT from lowest to highest, and finally the CALL NO from lowest to highest. In order for you to see which books were used the least, and when last they were checked in, and by sorting the call no. makes it easier for you to find the book on the shelves.
These are the steps I used:
1. CTRL + A
2. Select the Data tab
3. Click Sort
4. Add the following sort functions seen below:
However, these are the results I get, which is incorrect to what I wanted.
I've got a sheet linked to some live data that is constantly updating. I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.
I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?
I am trying to find a formula that will search a column in a pivot table find the highest % and rate it with a number. The one I'm currently working on has 8 variables, I would like it to see the highest % 29.17 and rate it 8, then find the next highest % 27.08 and rate that 7 and so on down to 0% and rate that 1. The cells in the pivot table may change over time so I can't do a formula that says if this cell = this than 8 as in the future that cell might have the lowest %.
Thank you in advance
I have 2 columns of data. I would like to find the 5 items in the first column that have the highest variance in the second column. Here is the data...
COL A COL B
The expected result for COL D would be...
The closest I got was to return the highest value using...
I'm thinking that I could start with this formula to find the first value, then would have to have 4 different formulas that remove the value found in the previous cell's formula.
Is this possible?
Thanks in advance!
Hopefully this will make sense!
I have a spreadsheet with many columns of data for each day. Is there any formula that I can use which will search the columns and give me the highest and lowest figure for that day?
H Column Header: Test A
I Column Header: Test B
J Column Header: Test C
I therefore need
Lowest (displayed in A1) Test A
Highest (displayed in B1) Test C
Thanks in advance!
Maybe some of you guys can help me.
I'm trying to find the a maximum mode and a minimum mode.
That means finding the lowest value that repeats the most and highest value that repeats the most.
And its not necessarily the lowest or highest value of the group for example.
You have 1,1,2,2,2,3,4,5,5,5,5,5,5,6,7,8,8,9,9,9,9,10,10,10
In this example:
The lowest is 1
The highest is 10
The mode is 5
What I'm trying to find is a formula that gets me:
The lowest mode 2 (lowest number that repeats the most)
The highest mode 9 (highest number that repeats the most)
I've been thinking and thinking but can't find anything.
Hope any of you guys can help.
I am hoping that one of the excel gurus can help!
I have a list of daily prices for a number of products. I am trying to find a way to work out, for the last value in each column, when this value is the highest since and lowest since.
So to explain further:
Column B has dates
Column C to I has the values, one column per product
Column J is blank, and columns K-T has some calcs, graphs and summary data.
The data currently runs to row ~1350, but they are not complete series, there are some blanks (public holidays etc).
What I would like is if the data ends at, row 1357, to have in K1357 "highest since dd/yy/mm" and K1358 "lowest since dd/yy/mm"; referring to the data in col C, the same in col L for data in D, etc.
I have tried a number of combinations of vlookups, if/match etc and not managed to get a result. Any help would be much appreciated
I have this link that pull Toronto, Ontario weather,
is there any way i can use a code in excel to pull this weather to a cell?
I need help creating a formula in excel to Identify the two Rows with the Highest and Lowest values in Column B EVERY 10 rows. THEN Identify those two rows by placing some text or number in Column C in only the rows with the highest and lowest values. For example I want to return the word "keep" in Column A3 for ONLY the Highest and Lowest trade price for each 10 row incement. This way I can sort the data by "Keep" and delete the other 8 rows of data. I can keep my data integrity and greatly reduce the size of the files.
I am using Excel 2007 and I have a 160,000 rows of data. The purpose of the formula is to cut this data down to by 80%
The spreadsheet below gives a simple example of what I need to do.
Time Price C is a blank column for formula out put
A B C
Row 1 19:30:25 121.81
Row 2 19:30:26 121.80 Keep
Row 3 19:30:29 121.83
Row 4 19:30:31 121.86 Keep
Row 5 19:39:35 121.84
Any help would be greatly appreciated.
I have a table, in column B17 to B3393 I have dates. In column H17 to H1029 I have the dailey high temperature, In column I17 to I1029 I have the dailey low temperature. In column J17 to J1029 I have the average dailey temperature. In column G17 to G1029 I have dailey gallons of heating oil used.There are blanks and zero's in all columns execpt the date column. Also I will update the table dailey. I need a formula that will give me the average high temperature of two dates in cell B3 (example 3/12/06) I have the from date and cell E3 (example 4/12/07) I have the to date.
In cell C5 I need the formula for the average high tempearture between the dates in cell B3 and E3 with the daily high temperature in column H.
In cell C6 I need the formla for the highest temperature between the dates in cell B3 to E3 with the dailey high temperature in column H.
In cell C10 I need a formula for the sum of the gallons of heating oil used between the dates in cell B3 and E3 with the gallons used in column G
I have excel 2003 and my operating system is XP. I have used sumif and sumproduct with 2 criteria with the 1st criteria in column A and the second criteria in column B but I need something with dates between with the dates in 1 column. I have found formulas with dates but for months and years not with 2 date in seperate cells. I have searched You Tubers Love Excel watching at least 100 videos on all subjects from offset to sumprpucts, index and match with no luck.
I have used excel for about 6 years now and have always been able to find an answer on this web site or may others, but this one hase me stumped.
Darryl (Dr Sawyer)
how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003. Any help appreciated. Rgds Nigel
I rarely use Excel so my knowledge of it is rather basic. Anyway I have a few hundred thousand lines of data in an excel sheet, and I would like to create a few graphs from it. The data is structured like so: first column is the date, then I have two other columns with numbers - one of them has high numbers and one of them has low numbers. There are typically over 1000 entries for each date.
What I want to do:
For every unique date in the main data source (i.e. Sheet1), I want to get the highest number from the "High" column and the lowest number from the "Low" column and plot the difference between those two (high minus low - i.e. the "range") on a graph. In my mind I see it like a bell curve, most days will have a similar-ish range and so should bunch up in the center. Fewer days will have substantially larger or smaller ranges and so will be the small outer part of the bell curve (my book on statistics should be arriving shortly, then I'll be able to say this stuff intelligibly lol). I'm guessing the x axis will be the difference number (i.e. if 20100101's highest high is 200 and lowest low is 50, then is should make a mark at 150 on the x axis - and if I get another with the same value then it would go above that one, so as to increase the height to show multiple entries at that number...)
In short, it should be able to find any unique date in the Date column and then get the high/low for that date from the 1000+ entries for that date anywhere in the data, and plot the difference between the two on a graph. And do this for the other 260-ish dates, which each also have +1000 entries each. I don't know the dates beforehand, it should be able to handle any unique dates thrown at it since there will be +10 million entries from lots of years (not all at once lol).
I'm using Excel 2007. Example of data attached.
Any help appreciated. Cheers
I have a sheet to record data but i need a formula change and not sure how to go about it.
In the above formula it will find the highest value, what i need to to do is only find the highest value if that value is 7 point higher than the second highest value.
I have a 46000 rows of data. I want to make a chart, but only want to plot the highest and lowest data points in the data set (plot a zig zag of the high and low of a specified time interval). How do I get Excel to automatically find and plot only the highest and lowest data points?
I have a spreadsheet with cells that update in real time. Is there a way to monitor the highest and lowest values in these cells? I would need to output the data to another cell so that every time the monitored cells hit a new high or low, the other cell would be updated. Thanks
I am using Excel 2003. I want to highlight the highest value in a given column. The worksheet starts with row 10 and ends on row 89. I select those rows (10 thru 89) by highlighting them and then go to Conditional Formatting and type in the following formula:
=E10=Max(E$10:E$89), select a color from Format/Patterns and click OK,OK.
I enter numbers in column E from row 10 downward. The highest value is not highlighted. there seems to be no consistency to which value is highlighted. sometimes a cell above row 10 is highlighted.
I need the highest value in columns E-O, row 10-89. I put what appears to be the appropriate formula in for each row and get the same inconsistencies.
Can someone advise me?
thanks in advance
Greetings everyone. I am taking a shot in the dark here. I am working on a project for my job, and everything is complete except for one thing. They would like to have a weather radar in the workbook. I have tried importing it like you would anything else from the web, but no luck. I have also looked thru MS excel help and the board and have not found anything useful. I did find that you all talk about the weather alot on these boards though. thanks for the help.
sorry, I am currently using Excel 2007, but also have acces to Excel 2003
Im still have issues with creating a formula for the Lowest and one for the Highest value in a range.
So if anyone has the winning formula, I would greatly apprecaite your assistance:
Let me explain
Range a1:a9 (1,21,1,8,12,12,8,8,1)
Expected Returned are
Most Common Lowest = 1
Most Common Highest = 8
Logic ( Highest - Although they are two occurances of '12' and twelve is higher than eight, there are 3x occurance of 8, so 8 wins). rule being that number of occurances overide range value.
Likewise same logic would apply for lowest eg
Range a1:a9 (1,21,1,2,12,12,8,2,2)
Highest = 12
Lowest = 2
3 occurances of 2, although 1 is lower and has 2xoccurance)
Range a1:a9 (1,21,1,4,12,12,8,2,2)
Highest = 12
Lowest = 2
2 occuranaces of '2' and 2 occurances of '1'......however '2' wins the Lowest as 2 is larger value than '1' in this match