Need help with if, index, and concatenate

[mcraven1971]

Hello all... I have a perplexing dilemna. When my coworkers enter data into the "DATA" sheet it is entered as follows:

A4: WELD ID
B4: FIRST MEASUREMENT
C4: SECOND MEASUREMENT
D4: AVERAGE OF FIRST AND SECOND MEASUREMENT

There will be upwards of 600 rows of data and 1,100 or more columns.

On a seperate sheet "CHART" I want to grab the data from the "DATA" sheet and analyze it. This is where my problem is. I am using the following statement to determine if the data passes (P) or fails (F). Anything under 5 will show up with an F and anything over will show up with a P:
=IF(DATA!G4="","",IF(DATA!G4<5,"F","P"))

I am also using this formula to report the average (Although I think I am using the column statement incorrectly):
=INDEX(DATA!4:4,((COLUMNS($F:F)-1)*3)+7)

I put the column statement in because I want the "CHART" sheet to automatically populate if a column of data on the "DATA" sheet is inserted.

My dilemna is that I want to combine these into one cell. Can someone please help? I have been racking my brain for hours.

Thanks!

 

[guerillaunit]

Are you looking to combine the pass / fail column with the average column?

 

[mcraven1971]

I think so... What I'd like to see in the CHART sheet is the average and the Pass/Fail in the same cell. For instance if the average ends up being 5.5, I'd like for the cell to read 5.5P.

 

[guerillaunit]

You can move the individual pass / fail column and the average column to fields that are out of the user's view (ie. columns IU and IV). Then in the field where you'd like the combined result to be, use concatenate.

ie. "=IU1 & IV1"

 

[mcraven1971]

That's how I had it but my customer wants to leave the three columns together so that the users get instant feedback. The customer only wants an Executive Summary once the data is put in place so they are asking for a seperate sheet where the average and Pass/Fail are reported. Any ideas you have are much appreciated. Thanks!