• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

="Total : "&A6 should work.

That was what I initially tried, but it'll give me the total in a different cell than the original one that contains the count function. What I'd like is for the count function total and the text to be contained in one cell if possible.
 
That was what I initially tried, but it'll give me the total in a different cell than the original one that contains the count function. What I'd like is for the count function total and the text to be contained in one cell if possible.

Sorry misunderstood, just replace A6 with your count function and multiplier.

="Total : "&COUNT()*x
 
Excel Help Thread

Wondering if someone can help me figure out formula for this situation.

I need to create a table that returns the given Temperature for a given depth (confusingly shown shown as Temp_ X m) sampled on the given day. The chart below shows the data array.

So, Cell F4 should show 20.2 (20.2 degrees at 0m on 8/27/42).

G4 should show N/A or False or whatever. L4 should show 20.1 (20.1 degrees at 1.5m on 8/27/42).

dphnaqX.jpg
 
Last edited:
=IF(F$2=MID($C4,FIND("_",$C4,1)+1,FIND(" ",$C4,1)-FIND("_",$C4,1)-1)*1,$D4,"N/A or False or whatever")

Paste that into cell F4, drag down and right to the edges of your table
 
the bbd tag made me laugh pretty hard

god, I wish i were mature like numbers
 
Bump.

I will preface this by saying I'm none too skilled at Excel, and am trying to make a workbook to be used by people who may not know that Excel exists. What I'm trying to do is create a workbook that will contain scoring data for a debate-like competition, and then (the tricky part), compile that data into a table of results.

For accuracy reasons, and due to the skill level of the people I expect to be using this workbook, I have to make the entry sheets look as much like the actual scoresheets as possible. Thus, I'm pretty much wedded to this format.



Ultimately, what I want is a final summary sheet that looks something like this.



However, because the teams and team codes will be in different spots on the spreadsheet for each round and aren't listed in a nice, clean column anywhere, I wasn't sure Excel would be able to pull the data directly from the scoresheets. Instead, what I planned to do was create round-by-round summaries like the one below that would capture the data from the scoresheets, and then the final results table would pull from those summaries.



Is that a reasonable way to attack the problem, or are there better ways to do it?
 
disclaimer: i spent no more than 4 seconds skimming your post.

have you thought about using a google form for the entry and having that feed into a google spreadsheet?
 
I'm currently practicing for getting out of jury duty next month and I can tell you with complete certainty that all of those people are guilty.
 
Since this thread resurfaced: I've had a problem explaining this to Google well enough to get an answer. I have five columns of data, I want to know which values are in at least two of the columns. I feel like I may have to assemble a master list as a first step.
 
How many data points in each column? Why can't you just eyeball it?
 
Since this thread resurfaced: I've had a problem explaining this to Google well enough to get an answer. I have five columns of data, I want to know which values are in at least two of the columns. I feel like I may have to assemble a master list as a first step.

You could use the countif formula to count the number of times a value appears in the data range. If you want, you could couple it with an IF statement to identify only the items with 2 or more.
 
You could use the countif formula to count the number of times a value appears in the data range. If you want, you could couple it with an IF statement to identify only the items with 2 or more.

Yeah this is the kind of direction I was heading with the 'master list'

How many data points in each column? Why can't you just eyeball it?

A thousand or so. But even if it were 10, the whole point is to establish a reusable method, eyeballs make mistakes.
 
Do you want a final list of all the values that are in there 2+ times? Or could you just use conditional formatting to draw your attention to them in the list?

Also, are you looking for the number of columns that value appears in that row, or whether or not that value appears anywhere in that column?

If the former, with only five columns, at most you could have a possible two duplicates within a row. The below formulas will identify the two duplicates in two separate columns, or if there's just one duplicate, it'll be identified just in the first column. Assume columns A-E are your five with data, and these formulas are being put into G and H.

Cell G2:
=IF(COUNTIF(A2:$E2,A2)>=2,A2,IF(COUNTIF(B2:$E2,B2)>=2,B2,IF(COUNTIF(C2:$E2,C2)>=2,C2,IF(COUNTIF(D2:$E2,D2)>=2,D2,""))))

Cell H2:
=IF(AND(COUNTIF(B2:$E2,B2)>=2,B2<>G2),B2,IF(AND(COUNTIF(C2:$E2,C2)>=2,C2<>G2),C2,IF(AND(COUNTIF(D2:$E2,D2)>=2,D2<>G2),D2,IF(AND(COUNTIF(E2:$E2,E2)>=2,E2<>G2),E2,""))))
 
Last edited:
I think your formula assumes row consistency. Example data:
Code:
1	1	2	16	1
2	1	12	17	2
3	1	13	18	3
4	9	14	19	24
5	10	15	20	25

Result list should be: 1, 2, 3
If you need a conceptual way to think about, pretend it's 5 days of beer purchases, and I need to know who bought beers on 2 or more days. And Patron 1 is a lush, particularly on day(column) 2.
 
Last edited:
I think your formula assumes row consistency. Example data:
Code:
1	1	2	16	1
2	1	12	17	2
3	1	13	18	3
4	9	14	19	24
5	10	15	20	25

Result list should be: 1, 2, 3
If you need a conceptual way to think about, pretend it's 5 days of beer purchases, and I need to know who bought beers on 2 or more days. And Patron 1 is a lush, particularly on day(column) 2.

If you paste my formulas in column G and H, leaving column F blank, my formulas yield just that, using your sample data, assuming no column or row labels. Results in column G and H:

Code:
A	B	C	D	E	F	G	H
1	1	2	16	1	 	1
2	1	12	17	2	 	2
3	1	13	18	3	 	3
4	9	14	19	24	 	 
5	10	15	20	25

Cell G1 is returning "1" because "1" is present in columns A, B, and E in row 2
Call G2 is returning "2" because "2" is present in columns A and E in row 3
Cell G3 is returning "3" because "3" is present in columns A and E in row 4

Column H is returning nothing because there are no other duplicates, for example if you replace the "2" in cell C1 with "16", cell H1 would return "16"
 
Last edited:
Or are you just taking one patron, let's say, 25, and seeing if he has any values ANYWHERE in columns A : D? (Which is what I suspect you mean by row consistency). With thousands of rows of data, I assume you'd have thousands of unique patrons, and every time you come across a new patron in any column, you'd have to run the test for him. Which means you're either going to have to run a test on every data point in the 1000x5 matrix to see if it's in any of the other four columns (in which case the tests and results would need to be populated in five columns, and your results would be in a 1000x5 matrix and need to be aggregated into a "master list," as you say), or you're going to have to come up with the master list first and run the test only once for each patron.
 
Last edited:
And if that's how you have your data, that's not organized well enough to create/run a simple set of formulas to identify what you're looking for without using pivot tables, etc.
 
Back
Top