if or case statement

[tbcomputerguy]

Nested Case Statement

I have a database that has three tournaments. You are allowed to drop the lowest tournament. If you miss the tournament it is counted as null. if you miss two tournaments you get the value of the one you bowled in as your total. i believe I should use a nested case statement as an if statement might get convoluted.

please see attachment

Book2.pdf

[comment]

What's to nest here?

[tbcomputerguy]

What's to nest here?

I can't figure out the formula to find the lowest tournament of the three and still calculate the correct total.

[comment]

How about =

Sum ( Game1 ; Game2 ; Game3 ) - Case ( Count ( Game1 ; Game2 ; Game3 ) = 3 ; Min ( Game1 ; Game2 ; Game3 ) )

Edited March 16, 2011 by comment

[tbcomputerguy]

Close, problem is; if they only bowl two tournaments, they both count. if they only bowl one, then that one counts. these tournaments are a pins over average. if their average is 250 and they bowl 1000 for 4 games then they receive a 0. If they are absent they receive a "dnb". so the formula looks at a tournament with a missing score and gives it a 0. if they have a t1:100, t2:dnb, t3:-5, it is showing a 100 total instead of 95.

Follow me.

[comment]

I am afraid it's too cryptic for me. Why don't you write it out so that a person with no knowledge in bowling (like me) can follow.

their average is 250 and they bowl 1000 for 4 games

I see only three games.

[tbcomputerguy]

Each bowler bowls 3 tournaments a year. they are allowed to drop the lowest tournament. I was using arbitrary numbers earlier (I apologize). As I mentioned earlier, these are pins over average tournaments. Each consisting of 4 games. So if the bowler has a 200 average and bowls 1000 for the tournament, he/she is +200. Therefore, after a season has passed and they have bowled all three tournaments, their +/- is what we are calculating. so for instance T1:+200, T2:-25, T3:+30 (lowest=-25), total = +230.

Now if a bowler missed the tournament, we give them a dnb. potentially a bowler may miss 1 or 2 or even all 3 (unlikely). so a record might look like T1:dnb, T2:+60, T3:-19 = (lowest=dnb)=total: +41 or another record may look like this T1:dnb, T2:+60, T3:dnb = (lowest=dnb)=total: +60.

Which gets me to my question, i have tried various nested if/then formulas and they do not work. So I thought I would try the case statement, because i could use case (this or this; then this...etc) but that got messed up as well.

Is that ok to follow, sorry about the previous explanations.

Dave

[comment]

Now if a bowler missed the tournament, we give them a dnb.

Why not leave the result empty? "dnb" is not a number, and it's not convenient to have text values when computing sums, averages and counts. I think my calculation above would work then.

[tbcomputerguy]

I am given the data in an excel spreadsheet. Same sheet all the time. I have a script to import it, and filter all dnb records and give them nothing. Not O (zero) as someone could possibly bowl right on their average for neither a plus or minus. So the field is blank.

Dave

[tbcomputerguy]

I can do it in excel like so:

TOURN1	TOURN2	TOURN3	TOTAL

-92	-69	47	-22

12	-65	-45	-33

69	148	214	362

-30	-96	-45	-75

	42	158	200

-30		124	94

		-20	-20

[comment]

I have a script to import it, and filter all dnb records and give them nothing.

You could modify the calculation to ignore the "dnb", e.g.

Let ( [

g1 = GetAsNumber ( Game1 ) ;

g2 = GetAsNumber ( Game2 ) ;

g3 = GetAsNumber ( Game3 ) 

];

Sum ( g1 ; g2 ; g3 ) - Case ( Count ( g1 ; g2 ; g3 ) = 3 ; Min ( g1 ; g2 ; g3 ) )

)

Or auto-enter a calculated value during the import, e.g.

Case ( Self ≠ "dnb" ; Self )

---

Not related directly to your question, but you should consider making each result a separate record in a related table.

[tbcomputerguy]

Ok here is a screenshot of the results with the new formula:

look at the second one, it shows 389 and not the 331 as it should.

[comment]

here is a screenshot of the results with the new formula

Which one exactly?

[tbcomputerguy]

look at the second name. it says its total is 389, it should be 331. Because they missed a tournament, default, they add the other two. They are allowed to drop the lowest, in this case the missed one. Another example, look at the name jeff watts, his total should be 4.

Thanks alot for your patience in this.

Dave

[comment]

I see only the results - but not the exact contents of your fields nor your formula. Why don't you post a file (preferably a simple test) to demo the problem?

[tbcomputerguy]

I found the problem. In the fields with dnb. my script was not finding and replacing them with "" and there was conditional formatting telling it if dnb then change text colour to white. It worked in the test file i created, so I couldn't understand what happened. I may have fixed it. Your formula works great.

I truly apologize for taking you time...my inexperience caused this. with some due-diligence this could have been averted.

dave