Forums

We want to share an important update with our Betfair Community users.

Over the coming months, the Betfair Community platform will be winding down. The final active day for the platform will be December 21st. From December 22nd, users will no longer be able to access the platform.

Your discussions, insights, engagement, and, most importantly, feedback have helped shape the platform and guided innovation. Your feedback continues to be important to us, and we will continue to listen via our dedicated feedback channels, as well as through product testing and innovations.

To share your feedback and experience, visit: https://support.betfair.com/app/home

General Betting

Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
swift-tuttle
29 Sep 11 12:56
Joined:
Date Joined: 22 Mar 04
| Topic/replies: 28,125 | Blogger: swift-tuttle's blog
If you have a set of values in a column and you arrive at a different set of values in another column by applying the same formula to each cell in the first column

is there a way to introduce a factor into the formula that takes account of the position of the value within the column?

e.g. (this is not the actual data but hopefully illustrates what I mean)

Cell a1 holds the value 10
Cell a2 holds the value 20
Cell a3 holds the value 24
Cell a4 holds the value 33
Cell a5 holds the value 37

Say the formula (to keep it simple) multiplies the values by 2 in each case and the values resulting from the formula are in cells b1 through to b5

If you wanted to change the formula so that it also added 10% if it was the top cell of the list (in our example a1), added 20% if it was the second cell in the list etc down to adding 50% if it was the last cell in the list, is there a function that you can use in the formula to do this?

In actuality, there will be several lists in the same workbook and the lists will be of varying length.
Pause • Switch to Standard View excel spreadsheet help please
Show More
Loading...
Report bushy • September 29, 2011 1:38 PM BST
=formula*(1+ROW(A1)*0.1)
Report swift-tuttle • September 29, 2011 6:05 PM BST
Thanks for your help bushy

I've tried it but unfortunately because I have several 'lists' of values within the same workbook and I would want the top cell of each list to be 1, it doesn't quite fit the bill

but if I introduce another column of values alongside each list, I think it should be workable
Report DaveEdwards • September 29, 2011 7:31 PM BST
Possibly set up a table on another worksheet with your multiplying factors and then do a vlookup to draw them in as appropriate throughout your spreadsheet?
Report swift-tuttle • September 29, 2011 9:34 PM BST
Yes that sounds like the way to do it Dave

now that i am reconciled to actually typing in the factors for each cell
Report ocean0201 • September 30, 2011 2:49 AM BST
create another worksheet with multiplication factor and use that in your formula.. if it is a simple series like increment of 10 should be fairly easy to do....
Report DaveEdwards • September 30, 2011 7:06 AM BST
Is there an echo in here?
Report Compound Magic • September 30, 2011 10:44 AM BST
Hi, I could not work it out myself so I asked the question on mr excel and got an answer.

The formula given only now needs to have the multiplier of A1* whatever tacked on to

=Topcell As ~ =Topcell*2 (if A1 was multiplied by 2)

http://www.mrexcel.com/forum/showthread.php?t=582427

Explains what to do.
Report swift-tuttle • September 30, 2011 9:01 PM BST
thanks very much for your time and effort compound magic
Post Your Reply
<CTRL+Enter> to submit
Please login to post a reply.

Wonder

Instance ID: 13539
www.betfair.com