Spreadsheet help

  • Thread starter Thread starter Sureboss
  • 16 comments
  • 930 views
Sureboss

Sureboss

Tanned and Lipstick'd
Premium
Messages
15,505
United Kingdom
UK
Right, I will try to explain what I want to do...

I'm trying to create a stat tracking spreadsheet for a game, which the devs couldn't be bothered to make one for (a proper in-game stat tracker), I think it'll be quite popular if I can get it working. But I've got stuck.

In Cricket, the overs bowled by a bowler will be recorded as 12.3, now this means he's bowled 12 overs and 3 balls, not 12.3 overs.

What I want to do is add 10 of these up, and I can't work out the formula for it, I've tried the DOLLARDE method, which hasn't worked.

So, say I've got 10 figures of 12.3

If I try and add them up, the formula will come out as 123, whereas, I want it to be 125, as there are 6 balls in an over, and not 10.

Can someone help me? I think I've explained that as well as I can.

So there will be 10 cells, spread across 5 sheets, with the formula on a 6th sheet. I know that doesn't change what I need to do, to get this working, but might help you understand what I'm trying to do.

Feel free to shout at me if you want me to explain it better.
 
Can you use two seperate columns, one for overs and one for balls? Complete ignorant to cricket, I'm confused as to what you are asking.
 
Yeah, I tried Wikiing it, and still dun geddit. Can you show us how you would manually do the math to get to 125?
 
Okay, so an over consists of 6 balls. So when a bowler has completed 12.3 overs (12 overs and 3 balls), mathematically it's 12.5/12 1/2 overs.

I've tried using fractions, but I can't seem to change the denominator to always be out of 6.

So 10 x 12.3, for doing this spreadsheet should be:

(10 x 12) + (10 x .5) It's trying to tell the spreadsheet/formula, to read the .3 (or whatever, could be 1/6, 2/6, 3/6/, 4/6, 5/6) as that. Rather than it reading it as 12.3, it needs to be 12.5.

I have had some Irish coffee tonight, so it's getting a bit hazy. Hmm, I think I've explained it well enough though with the above. I tried the fraction bit, but couldn't get it to work. Not sure if I was doing it wrong.

If I do the overs and ball thing, I get the same issue.

Say I have:
12.2
12.3
12.4
12.3
12.5
12.1

In over figures.

My formula with this is:

=SUM(B17;D17;F17;H176;J17;L17+C17;E17;G17;I17;K17;M17)

With those left of the + being overs, and those right being the balls.

Overs will always be a whole number. So we've got 6 x 12. If I leave those right as whole numbers, the formula throws out 90. as it's (72+(2+3+4+5+3+1)) which gives me 90. Now, if I put them as decimals, I get (72+(.2+.3+.4+.5+.3+.1)) = 73.8.

All those figures (which are my test figures on this spreadsheet), should equal , in cricketing terms. (72 overs + (18 balls - which equals 3 overs) 3 overs) = 75 overs.

I think that's explained better with an example.
 
Ah, okay, simple dimple – just use the integer function. E.g., to pull the left-of-the-decimal numbers, you’d do like int(a1), and to pull the numbers to the right and strip the decimal you’d do like 10*(a1-int(a1)). Then do math to your heart’s content.
 
Okay, another one, I'm half way there, my trial and error hasn't gone anywhere, so I'll try here.

Using IF.

=IF(B2>=100;1;0)

In that same formula, I also want cells B3, B4 to be tested. I've tried several options, but keep getting errors. I think I have an idea for a work-around, but it would have involve 10 more formulas than I think is necessary!

Thanks!
 
It's clunky, but if you're only testing 3 cells, you can nest the IF statements so that it handles them sequentially.
 
Hmm, that hasn't seemed to work properly.

Maybe I'm trying to do something that I can't.

I want all of B2, B3 B4 to be separately tested, but within the same formula. So if B2 >=100 then it's one. If B2 >=100, and B3 >=100, I want it to be 2. But if B2 >=100 but B3 is <100, then I want it to be 1. I need to do this for about 10 cells in total, over 5 worksheets.
 
Can you just do something as simple as:

=IF(B3>99.9,1,0)
=IF(B4>99.9,1,0)
=IF(B5>99.9,1,0)
=SUM(A3:A5)
 
Yes, that's what my back-up was, and I've resorted to it. :) Though mine differs in >=100. I save a whopping one character in that formula!
 
I must have done something wrong the first time. It wouldn't let me do the >= bit, hence the 99.9. Peculiar.
 
Basically, you need to work in base 6, not base 10 as our usual number system works, however it's complicated since only the decimals are in base 6 and the integers are still in base 10. There's unfortunately no easy easy way to get excel to do this without programming it into VBA module.
 
I did see something about VBA. Never done it. By looking at guides, etc, is it easy to use VBA?
 
Not overly easy, it's basically programming the thing to do something that doesn't come as standard. There's lots of good websites out there on it, but I tend to find someone who's already written the macro I want and copy and paste it, the internet is a wonderful thing!
 
Sage
Ah, okay, simple dimple – just use the integer function. E.g., to pull the left-of-the-decimal numbers, you’d do like int(a1), and to pull the numbers to the right and strip the decimal you’d do like 10*(a1-int(a1)). Then do math to your heart’s content.
Click to expand...

Right. To go back to this...

I've found a formula on the interweb.

=INT(G35)+(G35-INT(G35))*6/10

Where G35 is the formula. =SUM(G20:G24)

12.2
12.4
12.3
12.4
12.2

Are what is in the those cells.

In the SUM formula those equal 61.5, the Integer formula makes it equal 61.3, it should be 62.3. Can I change the Integer formula to make it equal 62.3?
 
Got the overs solved, thanks to my boss.

=(B2-ROUNDDOWN(B2;0))*10+ROUNDDOWN(B2;0)*6

Where B2 = figures in overs, this gives you the amount of balls.

Then,

=((SUM(C2:C6)/6-ROUNDDOWN((SUM(C2:C6)/6);0))*6)/10+ROUNDDOWN((SUM(C2:C6)/6);0)

Where, C cells are the overs calculated in balls. Now I can completely start building this spreadsheet!
 

Latest Posts

Back