Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple, yet complex problem! Using results as new data during calculations?
Hello,
This seems simple to me, but is so far baffling me on a way to accomplish it without spending hours manually writing formulas. So here's the problem. I essentially have a very large sheet of hard numbers. They are presented in a table format but are read diagonally, so that the cell to the top right is the next instance of an occurence. Here is an example of what I am dealing with: __A_____B_______C_______D__ 1|| 7 11 8 7 2|| 11 10 6 6 3|| 11 7 1 6 4|| 6 0 3 3 5|| 3 1 0 1 6|| 2 0 0 5 7|| 0 0 2 5 Look at D3, C4, B5, and A6. What I need to do is - if D3 is greater than C4, then add the difference of two to C4. The trick though is that I need the result of that new calculation (in this case, ((6-3)+3)=6 has to be the new variable for the calculation between C4 and B5. So rather than excel saying that B5 is 1 and C4 is 3, I need it to recognize that B5 is 1 and C4 is now 6, and thus turn B5 into 6 as well. Essentially I need excel to turn all diagonal rows of data into their highest entity, but still allowing for reductions to remain (if A6 were 7 for instance, nothing would happy as 7 is greater than 6). You would think a simple =if(d3c4,((d3-c4)+c4),c4) would work, but excel will only work with the original data and not the result. Is there any way to force what i want to happen? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple, yet complex problem! Using results as new data during calculations?
Sorry about the sloppy formatting there.
Anyway, I ended up resolving this but Im still VERY curious if there is a one shot solution. My solution was to copy the data directly below what I had, and then run the exact simple formula I had before with the greater (reference) number (ie. the 'top right cell' in all cases) coming from the copied data, and the smaller value coming from the original. It appears to work. S Davis wrote: Hello, This seems simple to me, but is so far baffling me on a way to accomplish it without spending hours manually writing formulas. So here's the problem. I essentially have a very large sheet of hard numbers. They are presented in a table format but are read diagonally, so that the cell to the top right is the next instance of an occurence. Here is an example of what I am dealing with: __A_____B_______C_______D__ 1|| 7 11 8 7 2|| 11 10 6 6 3|| 11 7 1 6 4|| 6 0 3 3 5|| 3 1 0 1 6|| 2 0 0 5 7|| 0 0 2 5 Look at D3, C4, B5, and A6. What I need to do is - if D3 is greater than C4, then add the difference of two to C4. The trick though is that I need the result of that new calculation (in this case, ((6-3)+3)=6 has to be the new variable for the calculation between C4 and B5. So rather than excel saying that B5 is 1 and C4 is 3, I need it to recognize that B5 is 1 and C4 is now 6, and thus turn B5 into 6 as well. Essentially I need excel to turn all diagonal rows of data into their highest entity, but still allowing for reductions to remain (if A6 were 7 for instance, nothing would happy as 7 is greater than 6). You would think a simple =if(d3c4,((d3-c4)+c4),c4) would work, but excel will only work with the original data and not the result. Is there any way to force what i want to happen? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple, yet complex problem! Using results as new data during calculations?
Sorry about the sloppy formatting there.
Anyway, I ended up resolving this but Im still VERY curious if there is a one shot solution. My solution was to copy the data directly below what I had, and then run the exact simple formula I had before with the greater (reference) number (ie. the 'top right cell' in all cases) coming from the copied data, and the smaller value coming from the original. It appears to work. S Davis wrote: Hello, This seems simple to me, but is so far baffling me on a way to accomplish it without spending hours manually writing formulas. So here's the problem. I essentially have a very large sheet of hard numbers. They are presented in a table format but are read diagonally, so that the cell to the top right is the next instance of an occurence. Here is an example of what I am dealing with: __A_____B_______C_______D__ 1|| 7 11 8 7 2|| 11 10 6 6 3|| 11 7 1 6 4|| 6 0 3 3 5|| 3 1 0 1 6|| 2 0 0 5 7|| 0 0 2 5 Look at D3, C4, B5, and A6. What I need to do is - if D3 is greater than C4, then add the difference of two to C4. The trick though is that I need the result of that new calculation (in this case, ((6-3)+3)=6 has to be the new variable for the calculation between C4 and B5. So rather than excel saying that B5 is 1 and C4 is 3, I need it to recognize that B5 is 1 and C4 is now 6, and thus turn B5 into 6 as well. Essentially I need excel to turn all diagonal rows of data into their highest entity, but still allowing for reductions to remain (if A6 were 7 for instance, nothing would happy as 7 is greater than 6). You would think a simple =if(d3c4,((d3-c4)+c4),c4) would work, but excel will only work with the original data and not the result. Is there any way to force what i want to happen? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Pasting Data | Setting up and Configuration of Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Data -> Table Problem | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Problem with external data | Excel Discussion (Misc queries) |