# Posts by boynejs

• ## Calculating Records

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.

• ## Calculating Records

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.

• ## Calculating Records

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

• ## Need to grab data from 2 different columns

Re: Need to grab data from 2 different columns

Thanks a lot for the help!

Jeff

• ## Need to grab data from 2 different columns

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

• ## Need Blank Cells with Countifs are counting alphanumeric characters & blank cells

Re: Need Blank Cells with Countifs are counting alphanumeric characters &amp; blank cells

Never would have thought of that one! Thanks so much.

Jeff

• ## Need Blank Cells with Countifs are counting alphanumeric characters & blank cells

Re: Need Blank Cells with Countifs are counting alphanumeric characters &amp; blank cells

Quote from Krishnakumar;732998

=IFERROR(1/(1/COUNTIFS(\$B\$4:\$B\$26,\$F4,\$A\$4:\$A\$26,\$E4)),"")

For more details how this works, see: IFERROR techniques

Thanks for the reply! This does well with the alphanumeric cells, but the blank cells are still counting and I need them blank too.

• ## Need Blank Cells with Countifs are counting alphanumeric characters & blank cells

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.

Jeff

• ## Ties in reverse order

Re: Ties in reverse order

Thanks! This works great.

Jeff

• ## Ties in reverse order

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

• ## Index Match producing zeros and #N/A errors

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

Quote from holycow;732374

=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

• ## Index Match producing zeros and #N/A errors

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

• ## Index Match producing zeros and #N/A errors

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

• ## Data only displayed if it is less than the % percentage of missing data

Re: Data only displayed if it is less than the % percentage of missing data

Quote from pike;732277

Hi boynejs
in C2
=IF(H\$1<B2,"",A2)
and fill down
The \$ is to fix the cell row in the formula when filling down

Thanks!

• ## Data only displayed if it is less than the % percentage of missing data

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...

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

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

However I wanted to make it more user friendly.

Jeff

• ## Copying Tables

Re: Copying Tables

This works great! Thanks for the help!

Jeff

• ## Copying Tables

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?

• ## Converting numeric values back to a alphanumeric character after ranking them.

Re: Converting numeric values back to a alphanumeric character after ranking them.

Thanks a lot for the help! It works great!

• ## Converting numeric values back to a alphanumeric character after ranking them.

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.

• ## AVERAGEIFS and SUMIFS when criteria can occur in 2 different columns

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.