hi Guys
I have a column with data that resembles below, this column is of infinite length.
E002356
E012356
E015689
E002365
E005897
I need a formula to find the highest figure in this column (in this case would be E015689) between a specific date range say from 1st Jan 2014 to 31st Jan 2014
thanks in advance
Simon
I assume the dates are in the next column. Is the text at the front of your data always one character, or can it be more?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
OK. No reply yet; but let's have a punt at an answer. try this (array formula) with a helper column to isolate the numberical parts.
Array formulas must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
hi
sorry for the delay the answer is yes the column will only ever have one letter followed by 6 digits, the date range I have put in cells away from the data eg below
1/1/2015 31/1/2015
1/2/2015 28/2/2015
and so on
thanks
simon
Is the anser in #3 of Glenn Kennedy, what you expected.
If not, add an excel file, without confidential information, together with the desired (expected) result.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Assuming figures are A1:A20; dates are B1:B20; J1:K1 is start-end date and downward
In L1 to find MAX:
=MAX(RIGHT(A1:A20,6)*(B1:B20-J1>=0)*(B1:B20-K1<=0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Quang PT
hi
I have attached a spread sheet with I think is a description of what I need the formula I copied and pasted came up with a number
error
thanks for all your help
Simon
With 2 helpcolumns and a pivot table.
See the attached file.
thanks for the reply I have copied the formula below
=RIGHT(I2,LEN(I2)-1)*1
to create just a number in a new column is there now a formula I can use to take the highest number between the dates I want
1/1/2015 to 31/1/2015 etc
thanks
simon
M2 =if($F$2:$F$500="yes",Large($G$2:$G$500,1))
hi guys
im putting the following formula in to the cell to find the largest figure and im having an error #value come up in the cell
=MAX(RIGHT(Data!D:D,6)*(Data!F:F-Y1>=0)*(Data!F:F-Z1<=0))
column D is where the number I want to find the highest and column F is where the dates are
hope you can help
thanks
Simon
hi guys
I copied and pasted the following formula in my spread sheet
=MAX(RIGHT(Data!D:D,6)*(Data!F:F-Y1>=0)*(Data!F:F-Z1<=0))
column D is where I want the highest value from and column F is where the dates are when I enter the cell comes up with an error #Value
could you explain what im doing wrong??
thanks
simon
See my solution in #10.
With the start date in G2 and the end date in H2 try this:
Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
See: http://www.excelforum.com/excel-form...two-dates.html msg#2
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
DUPLICATE POST - PLEASE DELETE
With the start date in G2 and the end date in H2 try this:
Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
See: http://www.excelforum.com/excel-form...two-dates.html msg#2
Last edited by newdoverman; 01-21-2015 at 02:09 PM.
worked a treat guys thank you all very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks