Posts by boynejs

    Re: Calculating Records


    Quote from PCI;741940

    If column F is right and column G is not ( see row 24) next formula could work for column G
    G3 =IF(ISERROR(MATCH(D3,D$2:D2,0)),0,IF(E3>=E2,MAX((D$2:D2=D3)*(IF(T$2:T2="",0,T$2:T2)))+1,""))
    Confirm using Control + Shift + Enter for array formula
    Then copy and drag down as needed


    Thanks for the help! What is in column T? I do not have that column in my original column.

    Re: Calculating Records


    Quote from PCI;741937

    Do you confirm that F24 is 1 when it is not greater than the max value for April 1st but greater than the previous value still in April 1st?

    A record can occur when the temperature is either tied or exceeded.

    I am trying to capture all of the temperature/precipitation records (both new and tied) for a climate site. In the attached table, I have the temperature data from March 31 and April 1. In column F, I want the first time that the record is tied or exceeded for a particular date to be denoted by 1...and every subsequent record for that date would be 1 higher. When a new date is analyzed, I want the count to start over. Column G show what I hope to get. After getting this, I would like to create a table similar to columns I through L by using Index Match based off of Column F and selected date for the analysis.


    For Column F, I tried


    =IF((AND(D18=D17,MAX($E$2:$E18)<=E18)),1,"")


    I thought it was working, but the problem I shortly realized that it had issues when you changed dates. I thought about using rank too, but then I am losing my chronological data that is also required.


    Any ideas?


    Jeff

    I have a data table which lists the season (column a) and then its start and end times for astronomical (columns B & C) and meteorological (columns D & E). In another spread sheet, the user will be entering the type of season which can be either astronomical or meteorological and then the season in the yellow boxes. Based on these inputs, I would like to access the data table and grab the appropriate begin and end dates for those selections. Does anyone have any ideas of how I can do this? I have included a spread sheet to show you what I mean.


    Thanks for any help!


    Jeff

    I am having issues with the following equation in Column H (Occurrences).


    =COUNTIFS($B$4:$B$26,$F4,$A$4:$A$26,$E4)


    It does well when I have numerical values.


    However, when I have missing values denoted by M in Column B it does not count them so I get zero. I actually need a blank in this situation, because 0 is a valid occurrence and when I average them at the end, these zeros at the end...it will mess up my results.


    My other issue is that I have dates in the future which do not have any data at all so they are blanks. This equation will count up these values as if they were numbers. I want a blank in these situations too...so it does not mess up my averaging at the end.


    Thanks in advance for any help you can provide.


    Jeff

    Re: Ties in reverse order



    Thanks! This works great.


    Jeff

    The following formulas do what I want...


    =IF(LARGE($G$3:$G$26,$K3)=0.001,"T",LARGE($G$3:$G$26,$K3)) in Cell L3


    =SUMPRODUCT(($A$3:$A$26)*($E$3:$E$26=L3)*($H$3:$H$26=COUNTIF($L$3:L3,L3))) in Cell M3


    They rank things in the proper order...


    Highest to lowest numbers above zero
    then traces
    then zeros


    My question is there a way to reverse the order of the years in column N when there is a tie. I would like the most recent year to be listed first and then work backward when there is a tie.


    [TABLE="width: 256"]

    [tr]


    [TD="class: xl63, width: 64"]9[/TD]
    [TD="class: xl66, width: 64"]0.010[/TD]
    [TD="class: xl64, width: 64"]25-Dec[/TD]
    [TD="class: xl65, width: 64"]1999[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]10[/TD]
    [TD="class: xl66"]0.010[/TD]
    [TD="class: xl64"]20-Dec[/TD]
    [TD="class: xl65"]2004[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]11[/TD]
    [TD="class: xl66"]T[/TD]
    [TD="class: xl64"]2-Dec[/TD]
    [TD="class: xl65"]1873[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]12[/TD]
    [TD="class: xl66"]T[/TD]
    [TD="class: xl64"]25-Dec[/TD]
    [TD="class: xl65"]1880[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]13[/TD]
    [TD="class: xl66"]T[/TD]
    [TD="class: xl64"]25-Dec[/TD]
    [TD="class: xl65"]1899[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]14[/TD]
    [TD="class: xl66"]T[/TD]
    [TD="class: xl64"]25-Dec[/TD]
    [TD="class: xl65"]1995[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]15[/TD]
    [TD="class: xl66"]T[/TD]
    [TD="class: xl64"]16-Dec[/TD]
    [TD="class: xl65"]1998[/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]16[/TD]
    [TD="class: xl66"]T[/TD]
    [TD="class: xl64"]29-Dec[/TD]
    [TD="class: xl65"]2001
    [/TD]

    [/tr]


    [/TABLE]



    For example, I would like 0.01 years to be listed as 2004 first and then followed by 1999. I have attached my sample spreadsheet for your reference.


    Jeff

    Re: Index Match producing zeros and #N/A errors


    Quote from holycow;732374

    How about


    =IFERROR(INDEX($B$5:$B$15,MATCH(1,(($A$5:$A$15=$E5)*(ISNUMBER($B$5:$B$15))*($B$5:$B$15=MAX(IF($A$5:$A$15=$E5,$B$5:$B$15)))),0)),"")


    CSE entered


    Thanks for the help again! I greatly appreciate it. Almost done with this project. :)


    Jeff

    Re: Index Match producing zeros and #N/A errors


    Quote from azumi;732351

    I think because you using MAX function, it just calculate numbers. Maybe more clear if you try to upload your small sample workbook and switch sensitive data with fake data but enough explain the problem.



    I simplified the equation to...


    =INDEX($B$5:$B$15,MATCH(1,(($A$5:$A$15=E5)*($B$5:$B$15=MAX(IF($A$5:$A$15=E5,$B$5:$B$15)))),0))


    I also added a sample spread sheet. I need the M (missing) and Blanks to be blanks.


    Thanks for any help!


    Jeff

    I have the following equation.



    =INDEX('Fill in Data'!$S$2:$S$62458,MATCH(1,(('Fill in Data'!$j$2:$j$62458=$A$5)*('Fill in Data'!$S$2:$S$62458=MAX(IF('Fill in Data'!$M$2:$M$62458=$A$5,'Fill in Data'!$S$2:$S$62458)))),0))


    It produces a #N/A error when it encounters a M and zeros when it encounters a blank cell. I need this equation to produce a blank in both situations.


    Any ideas on how I can this?


    Jeff

    I am trying to reference a cell (H1) as far as how much missing data I want to be allowed in order for the data from Column A to show up in Column C. I am using the following formula in Column C.



    =IF(H1>B2,"",A2)



    I am not sure why it is failing. By the way...I did ask a question on this before at the following link...



    http://www.ozgrid.com/forum/showthread.php?t=191899



    I was suggested to use the following formula which worked...



    =IF(C2>10%,"",A2)



    However I wanted to make it more user friendly.



    Jeff

    I have 2 tables. Both the astronomical (Table A) and meteorological (Table B) climate data will only fill when I search for it. These are helper tables. They will never be filled at the same time, so one of these 2 tables will always be blank. I need to combine the values of Tables A and B together into table C. I tried the simple sum formula in cell M4...



    =Sum(A4,G4)



    but I get 0. This will really mess up my results, so if there are 2 blank cells being combined. They must blank. Zero is not an option and conditional formatting to hide the zero will not either. Also if If I have a T (trace) or a M (missing) in either Table A or B, they must go over to Table C as a T or M respectively.



    Overall I want the filled table whether it is A or B transferred automatically to Table C. I included sample tables in the attached file.


    Any suggestions?

    Precipitation is measured to the nearest 0.01". When there is less than 0.01", we call this a Trace. In the spreadsheet attached, I want to rank the precipitation. I want them to be ranked by the following order



    1) Precipitation that measured (0.01" or higher)
    2) Traces - T
    3) No precipitation - 0"



    In order to do this, in helper row 1 I use the following formula to make Traces (T) = 0.001"



    =IF(E3="T",0.001,E3)



    I then use a second helper row to count the occurrences of each.



    =COUNTIF($G$3:G3,G3)



    In the ranking table in the far right, I then use



    =LARGE($G$3:$G$26,$K3)



    to rank them in the order that I wanted above. My question is whether there is a way in the table to convert the 0.001 (trace) values back to a T (column L) without messing up the date and year.

    Re: AVERAGEIFS and SUMIFS when criteria can occur in 2 different columns


    Quote from holycow;732012

    In Post #1 you said there would never be 2 in the same row


    Sorry about that! I was thinking that was the case, but when I started looking down the spread sheet noticed that there were some occasions. Never thought that would be the issue. Sorry about that.