Formula

[BobNTN]

Anyone care to tackle a formula for a query ?

fields = day1 got day2 (or cell values in Excel as in B1 and C1 and D1)

if 'got' is less than 'day1', 'day2' result would be value of 'day1' minus half the difference but don't round up
if 'got' and 'day1' are equal, 'day2' result is value of 'got'
if 'got' is greater than 'day1', 'day2' result would be value of 'day1' plus half the difference and round up

I have tried in Excel and in a query until I have a headache. I would take it in either.

Anyone generous enough - I appreciate
No one wants to, I understand.

 

[pbaldy]

Here's a stab:

Day2: IIf(Got < Day1, Day1 - Int((Day1 - Got)/2), IIf(Got = Day1, Got, Day1 + Int((Got - Day1)/2)))

 

[raskew]

Or -

Day2: Switch([got]<[Day1], [Day1] - Int(([Day1]-[Got])/2), [Got] = [Day1], [Got], True, [Day1} + Int(([Got]-[Day1])/2))

Bob

 

[BobNTN]

Thanks guys


Bob, just curious, why 'switch' ?

 

[raskew]

Bob, just curious, why 'switch' ?

Just a personal preference against nested Iif() statements. The Switch() function reduces the problems inherent with Iif()'s, e.g., formatting, matching parenthesis.

Bob

 

[BobNTN]

Well, they both work, no surprise.
However, they don't round up.
If the difference is plus and say, 3 or 5 (odd), I need it to round up as 2 or 3 respectively. I would +1 but that screws up an even result.
However, the minus result is working fine. I tried a round function, doesn't work.

 

[pbaldy]

I forget about Switch(); it's probably simpler here. To be honest, I don't use Switch() or nested IIf() much. At that point, I'm more likely to either create a lookup table or public function.

Forgot about the round up. One method is to test the result of the calculation less Int(calculation). If the difference is greater than 0, add one.

 

[raskew]

Hi BobNTN -

Would you provide some data examples where the round-up isn't working.

Bob

 

[pbaldy]

Just realized I'm surrounded by Bob's.

Any odd difference will produce the problem, Bob. Got = 4 and Day1 = 1 for instance. 4 minus 1 is 3, divided by 2 = 1.5, 1 after application of Int(). That other Bob wants it to be 2.

 

[BobNTN]

Correct Paul.
This is for a golf scoring program I am trying to do for a guy that runs a large tournament. It is scored on a point system and players points needed each day are adjusted by what they score the previous day.

If a player needs to score 16 points on Friday and he scores 20, then the points he would need to score on Saturday would be the points needed on Friday plus half the difference of what he actually scored. i.e. 20-16 =4/2 =2
16+2= 18 for Saturday.
If he needed 16 and scored 19, I need the 3 difference to round to 2.
If a player doesn't score the points he needs, he doesn't round up. Hence need 16 but scores 13, it would be 16 minus half the difference 3, 1.5 but not rounded so it would be 16-1=15. If the difference was minus 5, then it would be 2.5 or in this case, 16-2=14. The minus part works. As Paul said, the ODD plus is the problem.

 

[pbaldy]

I'm available Friday and Saturday...what are my tee times?

 

[BobNTN]

Actually, one tournament that I play in is Friday, Saturday, and Sunday in Lake Lure, NC.
This guy does little else but organize and run golf tournaments.

The point system we play is pretty neat. 2 man teams. Then they have a blind draw for foursome teams each day.

Rule is no player can help nor hurt his/her team more than 4 points each day, however, he/she is adjusted for the next day based on the full score.

First place team won $700 this year and he pays 3 places on the foursomes each day.

 

[pbaldy]

That does sound like fun. I used to go to NC a couple of times a year to visit my daughter, and usually played a little golf. She just transferred to England, so looks like I'll be playing there next!

 

[BobNTN]

I want to come out your way and see if you can get me a round with Natalie Gulbis.
I would love to play around with her! Or is that a round ?

 

[pbaldy]

Both would be my preference, but my wife might have issues with one of them.

Saw her in Palm Springs before the Nabisco, in short-shorts...nice legs.

 

[BobNTN]

Yessiree!!!
Of course, she could be my granddaughter but she's over 21!
Well, maybe young daughter.

 

[BobNTN]

did you figure out how I can round up on the positive odd results ?

 

[BobNTN]

Did you figure how I can round up on the positive odd results ?

 

[pbaldy]

I gave you a conceptual method in post 7. You would just add an IIf() there to make that test.

 

[BobNTN]

sorry
my bad