Putting a changing number in a cell (brought in by a macro) into a formula

**mrvp** · 09-10-2012, 04:34 PM

Hi,

Usual kind of query for me - something related to sports; in short I have attached a worksheet from one of my other workbooks. In short I can drop everything that I want in and out of the sheet (into another sheet and vice-versa - the other sheet is not in the attached) with a macro. The only thing that I can't get is my points spread adjustment. In short I'd like to use the points spread cell figure in B1 (which I can copy in via a macro - imagine it's a different figure for every line of the other sheet), to govern the sum in C177. In the case of the attached as B1 is 56.5, the formula in cell C177 is =SUM(C72:C128) - e.g. the sum of 0 points to 56 points (or under 56.5). If B1 was reduced to 56, it would be =SUM(C72:C127) - e.g. the sum of 0 points to 55 points (or under 56 points).

I'm figuring that it might be possible to do something with an index, match or substitute (but wouldn't know where to start)?

Incidentally the other maths in the spreadsheet is good for what I am trying to do (it may be possible to do this more elegantly - and I know it is theoretically possible a team could score more than 50 points e.g. to about 5 or 6 dp's - but right now I am just trying to solve this).

In short is this possible?

Thanks in advance, for any advice.

Poisson Spread Concept.xls

**Skinny Slim** · 09-10-2012, 11:06 PM

Hello,

I think this will get you there. Everything I added is in RED. It looks at the number in B1 (for this this to work, B1 must be a half number), subtracts 1, and rounds to the nearest whole number. e.g. 56.5 will yield 56. (you could alternatively and more simply subtract .5 from B1)

Next, I used VLOOKUP to match the 56 within column B and return the cumulative value in Column D (which I added). Again everything I added is in RED. You will have to cut/copy/Paste to make it look like you want. But the concept is there.

If I missed the boat on your question, please let me know!
Otherwise, please click the little star at the bottom left of this post!
Thanks...

-Slim

**mrvp** · 09-11-2012, 03:30 PM

Hi,

Thanks for the effort but I can't open it as I have 2004 so I can't do xlsx files. I have taken a guess at what you were trying to do and revised my original sheet a touch, so I have created a maximum, minimum and push cell - below B1 - to generate whatever the relevant figures would be based on whatever figure was put in for the spread (e.g. for 56.5, the min would be 56, and the max 57 and you can't have a push - I've also tried it for integers e.g. 56 returns, 55,56,57 so I know my logic is sound). I'm figuring as you are doing a vlookup based on those numbers this would help?

I've also added a tie% cell at C175 (the sport I was originally looking at didn't cater for a total tie - e.g. it was always offering non integers).

I've re-attached my thinking and am happy to bounce this around.

Thanks again.

Poisson Spread Concept_RevA.xls

**Skinny Slim** · 09-11-2012, 04:55 PM

This one is converted to Excel 97-03. See if you can open it...

**mrvp** · 09-11-2012, 05:47 PM

I like it. It wasn't quite what I was after - what I was thinking in my head (if that makes sense) - but it's given me enough to work with and I might put something back up here tomorrow when I've had a think about a few things about how to do what I'm trying to do (if that makes sense) and put it into something.

**Skinny Slim** · 09-12-2012, 08:53 AM

I'm glad it helps. Let me know if you need anything else!

-slim

**mrvp** · 09-12-2012, 05:16 PM

Originally Posted by Skinny Slim

I'm glad it helps. Let me know if you need anything else!

-slim

No - I'm good I think (see attached - with a little bit of workaround it handles the 56.5 or 56 issue - it's not wonderfully neat but it will do, this whole sheet will essentially just be a working out sheet in another workbook so I'm not worried). Thanks for the idea and I'm happy to stay in touch.

Poisson Spread Concept_RevB.xls