Jump to content

if or case statement


This topic is 4346 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 4346 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.