Forum Index Register Members List Events Mark Forums Read Help

 International Skeptics Forum Help needed with Excel.

 Welcome to the International Skeptics Forum, where we discuss skepticism, critical thinking, the paranormal and science in a friendly but lively way. You are currently viewing the forum as a guest, which means you are missing out on discussing matters that are of interest to you. Please consider registering so you can gain full use of the forum features and interact with other Members. Registration is simple, fast and free! Click here to register today.
 Tags excel , help

 12th April 2006, 07:25 AM #1 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Help needed with Excel. I only started using spreadsheets a few months ago so very new at this. Also I know virtually nothing about mathematics. Anyway I have thse values whereby y varies with x. I want to find a precise mathematical relationship between x and y so that if I input any x value I get the y value displayed. The values are these (2.2,1.6), (2.4,1.75), (2.6,1.92), (2.8,2.12), (3, 2.35) and (3.2,2.61). In each case x is the first number and y the second. These y values don't simply increase in value unformly but increase at a very slightly increasing rate. So how do I obtain a mathematical expression? equation? relating the two sets of numbers? I tried the chart function thingy last night and obtained a graph of these points. I was hoping that it might supply me with the relationship between these 2 sets of numbers. So I was messing about and discovered something called "add trendline". So I tried that. Experimented and found the expontial trendline gave the best fit. I ticked the R squared value and equation options. I've got on the chart R^2 = 0.999 and y = 0.5405*e^0.49x So it seems that it is giving me this highly obscure relationship between y and x I didn't know what "e" meant, but I have a vague feeling it's simply a number like pi with an infinite number of digits after the decimal point. I looked it up on google and some site said it's value is 2.71828. Unfortunately when I tried it, it gave incorrect values . For example when I put x as 3.2 in the equation it gave an answer of 2.82 when it is supposed to be 2.61 (and 2.82 is way above the trend line). So what gives here?
 12th April 2006, 07:44 AM #2 CFLarsen Penultimate Amazing   Join Date: Aug 2001 Posts: 42,367 Here's how: Turn off your computer. Don't turn it on again. Ever.
 12th April 2006, 07:45 AM #3 rats Muse   Join Date: Mar 2006 Posts: 608 Originally Posted by Interesting Ian For example when I put x as 3.2 in the equation it gave an answer of 2.82 when it is supposed to be 2.61 Here's a quick answer... For a beginner with Excel, sounds like you're doing very well! With y = 3.2, I get x = 2.6. For clarity: y = 0.5405 * e ^ (0.49*x) Good luck, rats.
 12th April 2006, 08:11 AM #4 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by rats Here's a quick answer... For a beginner with Excel, sounds like you're doing very well! With y = 3.2, I get x = 2.6. For clarity: y = 0.5405 * e ^ (0.49*x) Good luck, rats. I don't believe it! I just spent the last hour trying to figure it out, and adding more digits for "e" and right clicking the equation and expanding the digits after the decimal points there, and all the time I had neglected to put those brackets in! BTW is it not possible just to put e directly in rather than some approximation like 2.71828? I tried putting =0.5405*e^(0.49*Input!B10) but it just said "name". (Input!B10 is the cell where I put the value of x in).
 12th April 2006, 08:13 AM #5 Ziggurat Penultimate Amazing     Join Date: Jun 2003 Posts: 47,281 Originally Posted by Interesting Ian So it seems that it is giving me this highly obscure relationship between y and x I didn't know what "e" meant, but I have a vague feeling it's simply a number like pi with an infinite number of digits after the decimal point. I looked it up on google and some site said it's value is 2.71828. Yes, it is a special irrational number like pi. In fact, the two happen to be intimately related - you can kind of think of e being the "special" number for exponential functions the same way that pi is the "special" number for trigonomic functions. Here's two little equations which may pique your interest in e: $e^x = 1 + x + x^2/2! + x^3/3! + x^4/4! + ...$ Seems almost too simple, doesn't it? BTW, since you say you don't know much math, the ! notation means factorial. It's defined like this: $1! = 1, 2! = 1*2, 3! = 1*2*3, 4! = 1*2*3*4,$ and so on. Factorials get big very fast. The connection to pi comes from plugging in imaginary numbers to these equations, from which one can prove: $e^{\pi i} = -1$ __________________ "As long as it is admitted that the law may be diverted from its true purpose -- that it may violate property instead of protecting it -- then everyone will want to participate in making the law, either to protect himself against plunder or to use it for plunder. Political questions will always be prejudicial, dominant, and all-absorbing. There will be fighting at the door of the Legislative Palace, and the struggle within will be no less furious." - Bastiat, The Law
 12th April 2006, 08:20 AM #6 Unnamed Thinker   Join Date: Jun 2005 Posts: 230 Originally Posted by Interesting Ian BTW is it not possible just to put e directly in rather than some approximation like 2.71828? I tried putting =0.5405*e^(0.49*Input!B10) but it just said "name". (Input!B10 is the cell where I put the value of x in). Hi Ian, =0.5405*EXP(0.49*Input!B10) That should work. Good luck and nice job doing all of this from scratch.
 12th April 2006, 08:28 AM #7 Math Maniac Thinker     Join Date: Mar 2002 Posts: 161 A shorter exponential regression equation would simply calculate e ^ 0.49 and replace it to result in y = 1.6323 ^ x = (1 + 0.6323) ^ x. Most people are introduced/farmiliar with this as "Interest = Principal * (1 + r) ^ n" where r is the percentage increase (interest rate per period, when compounded more than once a year). A relationship such as this is not wholly obscure and using base 1.6323 instead of e ^ x form indicates that for every full unit of increase the value of x (e.g., from 2 to 3) the value of y will increase by approximately 63%. Whether or not your data or results are applicable at values previous or post your recorded data is another matter as you may only be allowed to interpolate (use the equation for values between your highest and lowest x values) as extrapolation (using your equation to predict values before and after your recorded x values) poses many other questions and concerns and chances for error in approximation. This is probably more detail than necessary but I'm in the library bored and thought I'd attempt to help (I typed this in between teaching classes so hopefully there are not m/any mistakes. Please let me know if you find some. Either way, considering yourself a 'beginner' on Excel and being able to do what you did is EXCELLENT. Good Luck!
 12th April 2006, 08:30 AM #8 Unnamed Thinker   Join Date: Jun 2005 Posts: 230 Originally Posted by CFLarsen Here's how: Turn off your computer. Don't turn it on again. Ever. CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment. Ignore the name "Interesting Ian". Is there a problem with the post itself?
 12th April 2006, 08:34 AM #9 I less than three logic Graduate Poster   Join Date: Dec 2005 Posts: 1,463 I did some quick calculations for you. I came up with a correlation coefficient of 0.9947 which made my r^2 = 0.98942809. The least-squares line I came up with was y = 1.0071x - 0.6609 Although, this line didn’t produce the exact values like you asked for, but working with statistics this is expected. The value for x = 3.2 returned y = 2.56182 which is a little closer than the 2.82 you had earlier. The closest value was for x = 2.4, which gave me y = 1.75614. … Hmm, seems that while I was working the problem the old fashion way I learned in statistics class (some paper and a calculator), some of you have already produced some better answers for him. __________________ “There is perhaps no better demonstration of the folly of human conceits than this distant image of our tiny world.” - Carl Sagan “The fact that we live at the bottom of a deep gravity well, on the surface of a gas covered planet going around a nuclear fireball ninety million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be.” – Douglas Adams Last edited by I less than three logic; 12th April 2006 at 08:48 AM.
 12th April 2006, 08:53 AM #10 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by Math Maniac Whether or not your data or results are applicable at values previous or post your recorded data is another matter as you may only be allowed to interpolate (use the equation for values between your highest and lowest x values) as extrapolation (using your equation to predict values before and after your recorded x values) poses many other questions and concerns and chances for error in approximation. Well the figures are to do with pre-match probabilities of soccer matches resulting in less than 2.5 goals i.e either 0, 1 or 2 goals. Thus the values 2.2, 2.4 etc represent the average expected number of goals there will be in the match. The values 1.6, 1.75 etc, represent the probabilities. Thus with an expected average of 2.2 goals there is a 1.6 average expected chance that there will be less than 2.5 goals in the entire match. Now, in practice, in professional soccer, the expected average will never dip below 1.9 goals in the match, nor will it exceed 3.4 (but of course there are many 0-0 and 3-2 scorelines etc -- it's the expected average we're talking about here). So I imagine that extrapolating above or below those values to any significant extent will not be applicable
 12th April 2006, 08:59 AM #11 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 And BTW, I much appreciate everyones help! Thanks!
 12th April 2006, 09:08 AM #12 Ripley Twenty-Nine Muse   Join Date: May 2005 Posts: 849 Originally Posted by Unnamed CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment. Ignore the name "Interesting Ian". Is there a problem with the post itself? You've echoed my thoughts as well.
 12th April 2006, 10:35 AM #13 LordoftheLeftHand Graduate Poster   Join Date: Jun 2005 Posts: 1,188 Quote: Help needed with Excel. It appears that your floobie index is off on the second plateau. Hope this helps. LLH
 12th April 2006, 10:55 AM #14 aggle-rithm Ardent Formulist     Join Date: Jun 2005 Location: Austin, TX Posts: 15,334 Originally Posted by Unnamed CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment. Ignore the name "Interesting Ian". Is there a problem with the post itself? It must be a programmer thang. We tend not to be a very couth bunch. I just about spit my drink all over my monitor when I read Claus' post. __________________ To understand recursion, you must first understand recursion. Woo's razor: Never attribute to stupidity that which can be adequately explained by aliens.
 12th April 2006, 11:00 AM #15 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by LordoftheLeftHand It appears that your floobie index is off on the second plateau. Hope this helps. LLH Eh? No. I have no idea what you mean.
 12th April 2006, 11:06 AM #16 alfaniner Penultimate Amazing     Join Date: Aug 2001 Posts: 23,676 Originally Posted by Interesting Ian Eh? No. I have no idea what you mean. Now that made me laugh. All the funnier as it was obviously a serious comment. __________________ Science is self-correcting. Woo is self-contradicting.
 12th April 2006, 11:16 AM #17 Molinaro Illuminator     Join Date: Dec 2005 Posts: 4,701 And now a little asside on the number e. 2.718281828459045.. is actualy quite easy to remember once someone points out the pattern. My grade 10 math teacher pointed it out and I have not forgotten yet.. 21 years later!! 2.7 1828 1828 45-90-45 __________________ 100% Cannuck!
 12th April 2006, 11:20 AM #18 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by alfaniner Now that made me laugh. All the funnier as it was obviously a serious comment. Who's comment? Mine or his? Whether his comment is serious or not, I have absolutely no idea what he means. I have never encountered the word "floobie". Even if I had I cannot imagine that it would make sense in the context of what he wrote. It's just words strung together meaninglessly so far as I can ascertain. So what's the joke?
 12th April 2006, 11:23 AM #19 brodski Tea-Time toad     Join Date: Mar 2005 Posts: 15,516 Originally Posted by Interesting Ian Who's comment? Mine or his? Whether his comment is serious or not, I have absolutely no idea what he means. I have never encountered the word "floobie". Even if I had I cannot imagine that it would make sense in the context of what he wrote. It's just words strung together meaninglessly so far as I can ascertain. So what's the joke? The joke was that he was clearly talking nonsense, whilst feigning helpfulness. The "hope this helps" comment was quite funny, but your response just made the joke.
 12th April 2006, 11:33 AM #20 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by brodski The joke was that he was clearly talking nonsense, whilst feigning helpfulness. The "hope this helps" comment was quite funny, but your response just made the joke. Gosh that was funny. I'm splitting my sides laughing. I fail to see what is amusing about ********* ruining this thread. I suspected it might be too good to last. There's always some people who endeavour to piss me off as much as possible. Damn shame.
 12th April 2006, 11:37 AM #21 brodski Tea-Time toad     Join Date: Mar 2005 Posts: 15,516 Originally Posted by Interesting Ian Gosh that was funny. I'm splitting my sides laughing. . It was funny yes, but clearly not meant in a malicious way, it was just a silly little joke. Originally Posted by Interesting Ian I fail to see what is amusing about ********* ruining this thread. I suspected it might be too good to last. There's always some people who endeavour to piss me off as much as possible. Damn shame. Larsens comment was completely uncalled for, but LLHs comment was just a little gentle ribbing, get over it.
 12th April 2006, 12:21 PM #22 Molinaro Illuminator     Join Date: Dec 2005 Posts: 4,701 LLH is the one who needs to 'get over it'. It's all fine and dandy to form an opinion about someone. However, when that opinion leaves you unable to differentiate between valid questions or discussions and nonsense, then it is you who should be shuned as much as any woo. The pettiness of the so-called enlightened one's on these forums is quite pathetic. __________________ 100% Cannuck!
 12th April 2006, 12:28 PM #23 LordoftheLeftHand Graduate Poster   Join Date: Jun 2005 Posts: 1,188 Originally Posted by Interesting Ian Whether his comment is serious or not, I have absolutely no idea what he means. I have never encountered the word "floobie". Even if I had I cannot imagine that it would make sense in the context of what he wrote. It's just words strung together meaninglessly so far as I can ascertain. So what's the joke? You are right that is all it was. I saw you were having a serious conversation and I couldn't resist derailing it with random nonsense. Sorry. LLH
 12th April 2006, 03:40 PM #24 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by LordoftheLeftHand You are right that is all it was. I saw you were having a serious conversation and I couldn't resist derailing it with random nonsense. Sorry. LLH I'm sorry too Sometimes I get ridiculously bad tempered over relatively trivial things. I was just a bit touchy after Larsen's unkind words.
 12th April 2006, 06:16 PM #25 aggle-rithm Ardent Formulist     Join Date: Jun 2005 Location: Austin, TX Posts: 15,334 Originally Posted by Interesting Ian I'm sorry too Sometimes I get ridiculously bad tempered over relatively trivial things. I was just a bit touchy after Larsen's unkind words. Same here. I confess I hadn't actually read Ian's post thoroughly before spewing my drink at the response. Something about the use of whimsical emoticons in the middle of a serious discussion of mathematics and computer software just seemed to be begging for a crass response... __________________ To understand recursion, you must first understand recursion. Woo's razor: Never attribute to stupidity that which can be adequately explained by aliens.
 12th April 2006, 11:51 PM #26 LordoftheLeftHand Graduate Poster   Join Date: Jun 2005 Posts: 1,188 Originally Posted by aggle-rithm Same here. I confess I hadn't actually read Ian's post thoroughly before spewing my drink at the response. Something about the use of whimsical emoticons in the middle of a serious discussion of mathematics and computer software just seemed to be begging for a crass response... Oh I read it before I posted, I just couldn't resist the urge to turn the tables on him (even though it was kind of childish). LLH
 14th April 2006, 03:32 PM #27 Lucky Graduate Poster     Join Date: Apr 2004 Location: Yorkshire Posts: 1,180 Ian, I’m baffled by your account of the soccer match data, for 2 reasons: 1) Your Y-values aren’t probabilities (and they go the wrong way). 2) Whatever they are, I suppose the Y-values were derived from the X-values, so I don’t understand why you want to find the relation by regression analysis. You can’t re-create a defined relationship from the numbers, unless you know the form of the equation and just want to calculate the coefficients (and even then you will have rounding errors). Please give more details!
 14th April 2006, 05:12 PM #28 alfaniner Penultimate Amazing     Join Date: Aug 2001 Posts: 23,676 Lucky, before you waste any more time, read this... Originally Posted by Interesting Ian ...I know virtually nothing about mathematics. ... __________________ Science is self-correcting. Woo is self-contradicting.
 14th April 2006, 07:36 PM #29 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by Lucky Ian, I’m baffled by your account of the soccer match data, for 2 reasons: 1) Your Y-values aren’t probabilities (and they go the wrong way). They are probabilities. Perhaps it would help if I pasted in a couple of the tables here. (Oops, just finished my post and discovered that I'm unable to paste in properly. Why the hell can't we paste something simple like a table!) HOW MANY GOALS WILL THERE BE? PRE-MATCH EXPECTATION: 2.2 So far 0 So far 1 So far 2 MINUTE Under 2.5 Over 2.5 Under 2.5 Over 2.5 Under 2.5 Over 2.5 1.00 1.60 2.66 - - - - 6.00 1.55 2.83 2.64 1.61 7.95 1.14 11.00 1.49 3.05 2.48 1.68 7.25 1.16 16.00 1.43 3.33 2.32 1.76 6.57 1.18 21.00 1.37 3.67 2.17 1.86 5.94 1.20 26.00 1.32 4.10 2.02 1.98 5.35 1.23 31.00 1.27 4.65 1.89 2.12 4.81 1.26 36.00 1.23 5.35 1.77 2.31 4.31 1.30 41.00 1.19 6.28 1.65 2.54 3.86 1.35 46.00 1.15 7.86 1.53 2.90 3.37 1.42 51.00 1.12 9.70 1.44 3.30 3.01 1.50 56.00 1.09 12.60 1.35 3.88 2.67 1.60 61.00 1.06 17.34 1.27 4.75 2.36 1.74 66.00 1.04 25.73 1.20 6.12 2.07 1.93 71.00 1.02 42.35 1.13 8.52 1.82 2.22 76.00 1.01 81.58 1.08 13.37 1.59 2.68 81.00 1.00 205.11 1.04 26.21 1.39 3.55 86.00 1.00 895.32 1.01 90.78 1.21 5.66 PRE-MATCH EXPECTATION: 3.2 So far 0 So far 1 So far 2 MINUTE Under 2.5 Over 2.5 Under 2.5 Over 2.5 Under 2.5 Over 2.5 1.00 2.61 1.62 - - - - 6.00 2.44 1.69 5.24 1.24 20.13 1.05 11.00 2.27 1.79 4.73 1.27 17.61 1.06 16.00 2.11 1.90 4.25 1.31 15.29 1.07 21.00 1.96 2.04 3.81 1.36 13.20 1.08 26.00 1.82 2.22 3.40 1.42 11.34 1.10 31.00 1.69 2.45 3.04 1.49 9.71 1.11 36.00 1.57 2.74 2.72 1.58 8.29 1.14 41.00 1.47 3.12 2.43 1.70 7.06 1.16 46.00 1.36 3.76 2.13 1.88 5.81 1.21 51.00 1.29 4.49 1.92 2.08 4.94 1.25 56.00 1.22 5.63 1.73 2.38 4.15 1.32 61.00 1.15 7.47 1.55 2.81 3.47 1.41 66.00 1.10 10.65 1.40 3.49 2.88 1.53 71.00 1.06 16.83 1.27 4.66 2.38 1.72 76.00 1.03 31.11 1.17 6.94 1.97 2.03 81.00 1.01 75.15 1.09 12.63 1.62 2.62 86.00 1.00 317.69 1.03 36.85 1.33 4.06 I have 6 such tables which I copied from a book (the definitive guide to betting exchanges). These are the probabilities that the author reckons hold for there either being less or more than 2.5 goals in a football (soccer) match as the match progresses. It lists the probabilities for there being so far 0, 1 or 2 goals. The 6 tables represent the pre-match expectation of 2.2, 2.4, 2.6, 2.8, 3.0, and 3.2 Goals of which I've just pasted in the first and last. The figures I quoted in my opening post are from the first minute (i.e right at the beginning of the match) for there being under 2.5 goals as the pre-match expectation of goals increases from 2.2 to 3.2 goals. Now the greater number of goals we expect on average before the match, the less probable that there will be less than 2.5 goals. That is why y decreases. What I'm going to do is to have just one table, and when I enter the pre-match goal expectation in a cell it will tell me all the probabilities for getting less or more than 2.5 goals as the match progresses. I needed to find the relationship between the probability for under and over 2.5 goals for each 5 minute interval in the game as the pre-match goal expectation increases. That way I can enter any value into the cell eg a pre-match expectation of 2.7 goals, and obtain all the appropriate probabilities (the mathematical relationship I obtained told me the probability for a table generated by inputting a pre-match goal expectation wasn't simply half way between the values in a 2.6 and 2.8 tables). I also intend to generate tables for the probabilities for there being under and over 1.5 goals as the match progresses, and the same goes for under and over 3.5 goals, under and over 4.5 goals, under and over 5.5 goals and under and over 6.5 goals. Why am I doing all this? I expect everyone has guessed. It is for the purposes of gambling on the total number of goals in football matches whilst a match progresses. Quote: 2) Whatever they are, I suppose the Y-values were derived from the X-values, so I don’t understand why you want to find the relation by regression analysis. Regression analysis?? What's that? The thing is I don't know where the guy who produced the tables got his figures from. He simply says this is what he reckons the probabilities are. Actually I did discover yesterday where he got the figures from for the probabilities before the match, or in the first minute. They are obtained from applying a poisson distribution to the average pre-match expected goal total. (I don't know what a poisson distribution is, but I don't need to because excel has it built in!). However I'm unable to determine why the probabilities diminish and increase the way they do as teh match progresses. I would have thought that the probabilities would decrease and increase uniformly (linearly?). But they don't. Quote: You can’t re-create a defined relationship from the numbers, unless you know the form of the equation and just want to calculate the coefficients (and even then you will have rounding errors). Please give more details! You'll need to speak in English. I don't understand what co-efficients mean. Anyway I've provide much more detail, so hopefully you should understand what I'm doing.
 14th April 2006, 07:42 PM #30 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by alfaniner Lucky, before you waste any more time, read this... Originally Posted by Interesting Ian : ...I know virtually nothing about mathematics. What little mathematics I've done I was much better at than any of my fellow pupils at school. Indeed in my 4th year exam when I was 14 I got the highest mark ever in the history of the school. But I never did any more mathematics after 16, and I've never done any statistics whatsoever (and certainly have never done any calculus and the like).
 14th April 2006, 07:56 PM #31 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by Lucky Ian, I’m baffled by your account of the soccer match data, for 2 reasons: 1) Your Y-values aren’t probabilities (and they go the wrong way). Why are you saying the y values aren't probabilities? I'm using decimal notation. 1.6 means that there is a 100/1.6 percent of a chance of the event happening i.e 62.5%
 15th April 2006, 05:08 AM #36 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by Art Vandelay However I'm unable to determine why the probabilities diminish and increase the way they do as teh match progresses. I would have thought that the probabilities would decrease and increase uniformly (linearly?). But they don't. Suppose there's a probability p of scoring at least once within a five minute period. If the team has already scored twice, all you care about is whether they score once more. Scoring two more goals, three more goals, etc. isn't any different to you, is it? So in a ten minute period, there are four possibilities: score nothing ((1-p)^2) score in first five minute period, but not in second (p(1-p)) score in second five minute period, but not in first ((p-1)p) score in both (p^2) So the total probability is the sum of the last three: p((2-2p)+p)=p(2-p) Notice that the last one doesn't count for any more than the middle two; the extra goal(s) are "wasted". You therefore get less than 2p. If you were to count the last one twice because there are twice as many goals, you would get 2p, which is what you (incorrectly) expected. Furthermore, if it were linear, then there would be a point at which the probability is more than 100%, which is absurd, isn't it? Yes the probabilities can't decrease uniformly. I'm not thinking there. I had in mind that the average number of goals in any remaining period should decrease uniformly. So with pre-match expectation of 2.2 goals, the half time expectation with no goals scored so far would be half of that. But if I enter 1.1 and do a poisson distribution, the figures don't tally. Have to think about this.
 16th April 2006, 05:32 AM #37 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 How do you link cells both ways? In other words changing the value in cell A so that it will automatically change the value in cell B to the same value, and also changing the value in cell B so that it will change the value in cell A to the same value? Another question. Is it possible to keep changing a value in a cell so that after every 5 minute interval after a specific time that I can specify, the cell will display the value of a succession of differing cells?
 16th April 2006, 08:09 AM #38 brodski Tea-Time toad     Join Date: Mar 2005 Posts: 15,516 Originally Posted by Interesting Ian How do you link cells both ways? In other words changing the value in cell A so that it will automatically change the value in cell B to the same value, and also changing the value in cell B so that it will change the value in cell A to the same value? I don't think you can, that would be circular reference, because (fr instance) if b=a=b you will never get an answers unless you define either "a" or "b". I don't think I'm explaining myself to clearly here. Right, if excel need a value of A to determine a value of B, but needs a value of B to determine a value of A, it cannot define either, as the equation will keep looping back on itself. ETA- if you can be clear about exactly why you want a=b=a=b etc, maybe I can find a way around it.
 16th April 2006, 09:13 AM #40 varwoche Penultimate Amazing     Join Date: Feb 2004 Location: Puget Sound Posts: 15,156 Originally Posted by Interesting Ian How do you link cells both ways? In other words changing the value in cell A so that it will automatically change the value in cell B to the same value, and also changing the value in cell B so that it will change the value in cell A to the same value? The Goal Seek feature might be applicable, no pun intended. __________________ To survive election season on a skeptics forum, one must understand Hymie-the-Robot. My authority is total - Trump

International Skeptics Forum