Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
Perhaps I'm not even phrasing the question correctly. I am arguably loathsome at math.
Given two ranges: A B 1000 500 2000 1000 3000 1500 If I have a variable that gives me 1750 and I want to reference that to column A... How can I get the difference calculated from column B? Example: 1750 finds the part of the A1:A3 range in which it fits (A1:A2), then applies its own difference between the two (75%) to the B1:B2 for an answer of 875. Of course, IRL column A is not comprised of even thousands. I hope that is clear enough. Thank you for your time. Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
On Mon, 8 Oct 2012 18:50:49 -0700 (PDT), TheMilkGuy wrote:
Perhaps I'm not even phrasing the question correctly. I am arguably loathsome at math. Given two ranges: A B 1000 500 2000 1000 3000 1500 If I have a variable that gives me 1750 and I want to reference that to column A... How can I get the difference calculated from column B? Example: 1750 finds the part of the A1:A3 range in which it fits (A1:A2), then applies its own difference between the two (75%) to the B1:B2 for an answer of 875. Of course, IRL column A is not comprised of even thousands. I hope that is clear enough. Thank you for your time. Cheers There are two ways to approach the problem. Not exactly what you requested, but sometimes what people with this sort of question really want, is the equivalent of a graphic solution. In other words, if the values are plotted on a two dimensional graph, and given a new x, what is the corresponding y value, and also assuming that the line representing that data is a straight line (although other shapes are possible with different equations), then the solution could be given by the FORECAST function: =FORECAST(Variable,ColB,ColA) On the other hand, if you are interested ONLY where Variable fits with regard to the values on either side, ignoring the remaining values, then you could still use the FORECAST function, but the computation of the x's and y's to use becomes more complex: =FORECAST(Variable,INDIRECT(ADDRESS(MATCH(Variable ,ColA),2) &":"&ADDRESS(MATCH(Variable,ColA)+1,2)), INDIRECT(ADDRESS(MATCH(Variable,ColA),1)&":" &ADDRESS(MATCH(Variable,ColA)+1,1))) But, since INDIRECT is a volatile function, it becomes more time-consuiming. To do the same thing without using Volatile functions, you could use this: =(Variable-INDEX(ColA,MATCH(Variable,ColA)))/(INDEX( ColA,MATCH(Variable,ColA)+1)-INDEX(ColA,MATCH( Variable,ColA)))*((INDEX(ColB,MATCH( Variable,ColA)+1))-(INDEX(ColB,MATCH( Variable,ColA))))+INDEX(ColB,MATCH(Variable,ColA)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
On Mon, 8 Oct 2012 18:50:49 -0700 (PDT), TheMilkGuy wrote:
Perhaps I'm not even phrasing the question correctly. I am arguably loathsome at math. Given two ranges: A B 1000 500 2000 1000 3000 1500 If I have a variable that gives me 1750 and I want to reference that to column A... How can I get the difference calculated from column B? Example: 1750 finds the part of the A1:A3 range in which it fits (A1:A2), then applies its own difference between the two (75%) to the B1:B2 for an answer of 875. Of course, IRL column A is not comprised of even thousands. I hope that is clear enough. Thank you for your time. Cheers And here's another formula looking at only the values on either side; also volatile but shorter: =FORECAST(Variable,OFFSET(INDEX(ColA,MATCH( Variable,ColA)),0,1,2,1),OFFSET(INDEX(ColA,MATCH( Variable,ColA)),0,0,2,1)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
Hi Ron,
First of all, I appreciate the quick reply. The second formula you sent works *perfectly* and I am more than pleased with the result. Saves me about a dozen nested IF's. Thank you very much. Cheers, Craig On Tuesday, October 9, 2012 8:32:22 AM UTC-3, Ron Rosenfeld wrote: On Mon, 8 Oct 2012 18:50:49 -0700 (PDT), TheMilkGuy wrote: Perhaps I'm not even phrasing the question correctly. I am arguably loathsome at math. Given two ranges: A B 1000 500 2000 1000 3000 1500 If I have a variable that gives me 1750 and I want to reference that to column A... How can I get the difference calculated from column B? Example: 1750 finds the part of the A1:A3 range in which it fits (A1:A2), then applies its own difference between the two (75%) to the B1:B2 for an answer of 875. Of course, IRL column A is not comprised of even thousands. I hope that is clear enough. Thank you for your time. Cheers And here's another formula looking at only the values on either side; also volatile but shorter: =FORECAST(Variable,OFFSET(INDEX(ColA,MATCH( Variable,ColA)),0,1,2,1),OFFSET(INDEX(ColA,MATCH( Variable,ColA)),0,0,2,1)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
On Tue, 9 Oct 2012 06:14:45 -0700 (PDT), TheMilkGuy wrote:
Hi Ron, First of all, I appreciate the quick reply. The second formula you sent works *perfectly* and I am more than pleased with the result. Saves me about a dozen nested IF's. Thank you very much. Cheers, Craig Glad to help. Thanks for the feedback. Actually, both the 2nd (using INDIRECT) and 3rd (using OFFSET) formulas should provide the same results (the 3rd formula is in my second post) and the 3rd might be easier to understand and maintain. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
I changed to the 3rd formula and you are right, it is much easier to understand. :)
Thanks again On Tuesday, October 9, 2012 3:10:20 PM UTC-3, Ron Rosenfeld wrote: On Tue, 9 Oct 2012 06:14:45 -0700 (PDT), TheMilkGuy wrote: Hi Ron, First of all, I appreciate the quick reply. The second formula you sent works *perfectly* and I am more than pleased with the result. Saves me about a dozen nested IF's. Thank you very much. Cheers, Craig Glad to help. Thanks for the feedback. Actually, both the 2nd (using INDIRECT) and 3rd (using OFFSET) formulas should provide the same results (the 3rd formula is in my second post) and the 3rd might be easier to understand and maintain. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Difference between cells based on variable...
Hi again Ron,
I am sorry to resurrect a topic, but I'm in need of a bit more assistance. I keep getting "Circular Reference" warnings associated with the formula you so kindly provided me. In most cases I can make them go away (example: A1 value of 1000, A4 value of 7000 - I change A1 to 999 and A4 to 7001) however, some I just cannot get rid of. I suppose not fully understanding the formula is my problem (repeat: it IS my problem) but I was wondering if you could a)dumb-it-down a bit for my understanding or b) perhaps edit it in some way to circumvent this issue? Again, many thanks for the help. Craig On Tuesday, October 9, 2012 3:10:20 PM UTC-3, Ron Rosenfeld wrote: On Tue, 9 Oct 2012 06:14:45 -0700 (PDT), TheMilkGuy wrote: Hi Ron, First of all, I appreciate the quick reply. The second formula you sent works *perfectly* and I am more than pleased with the result. Saves me about a dozen nested IF's. Thank you very much. Cheers, Craig Glad to help. Thanks for the feedback. Actually, both the 2nd (using INDIRECT) and 3rd (using OFFSET) formulas should provide the same results (the 3rd formula is in my second post) and the 3rd might be easier to understand and maintain. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i count text cells in excel based on variable criteria? | Excel Worksheet Functions | |||
Difference between fixed and variable date | Excel Programming | |||
Difference between 2 dates, incl weekends, with variable work days | Excel Discussion (Misc queries) | |||
Difference between 2 dates, incl weekends, with variable work days | Excel Worksheet Functions | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions |