Filling down a formula in Excel to a sparse range Announcing the arrival of Valued Associate...
Is CEO the "profession" with the most psychopaths?
Hangman Game with C++
What is a fractional matching?
How would a mousetrap for use in space work?
Why weren't discrete x86 CPUs ever used in game hardware?
Significance of Cersei's obsession with elephants?
Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?
Illegal assignment from sObject to Id
What do you call the main part of a joke?
Using audio cues to encourage good posture
Central Vacuuming: Is it worth it, and how does it compare to normal vacuuming?
How fail-safe is nr as stop bytes?
What's the meaning of "fortified infraction restraint"?
Why do early math courses focus on the cross sections of a cone and not on other 3D objects?
Dating a Former Employee
How to write the following sign?
How often does castling occur in grandmaster games?
Is it fair for a professor to grade us on the possession of past papers?
Did Deadpool rescue all of the X-Force?
Why is the AVR GCC compiler using a full `CALL` even though I have set the `-mshort-calls` flag?
Why does the remaining Rebel fleet at the end of Rogue One seem dramatically larger than the one in A New Hope?
Why should I vote and accept answers?
Chinese Seal on silk painting - what does it mean?
What would you call this weird metallic apparatus that allows you to lift people?
Filling down a formula in Excel to a sparse range
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)Fill sparse rows in ExcelExcel formula - querying a Range that results a RangeHow to autofill excel cells between a range using formulaDrag excel array formula left then downExcel- dragging down formula without using transposeExcel formula for repeating sequence down columnExcel 2016 pull down formulaExcel indirect drag down formulaExcel Formula, Get Last Row where Range is dynamicExcel formula to check on date falls within a range
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am creating an excel sheet out from the sounding table book of a fuel storage tank where the volume values were given for every fifth centimeter. I have to populate the empty cells with the interpolation values to use VLookUp function afterwards in order to get volume as a function of sounding (first column) and trim (x axis). I managed to insert four empty lines between each given values. Then I fill the cells in between by interpolating the values of upper and lower rows. Then I copy the interpolation range and paste it to the empty range. This is repeated hundreds of times. So I wonder if there could be an easier way as my total number of tanks is about 20 and the depth (y axis) is about 10000 cm. The problem is that if I copy and paste only a formula the empty destination cell is still overwritten to empty value. Otherwise I could reuse the formulas from the first tank.
...
...
125 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
126 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
127 474.7 474.1 473.5 472.9 472.3 471.6 471.0 470.4 ...
128 473.9 473.3 472.8 472.1 471.5 470.9 470.3 469.6 ...
129 473.2 472.6 472.0 471.3 470.7 470.1 469.5 468.8 ...
130 472.4 471.8 471.2 470.6 469.9 469.3 468.7 468.0 ...
131
132
133
134
135 469.3 468.7 468.0 467.4 466.8 466.2 465.5 464.9 ...
136
137
138
139
140 466.1 465.5 464.9 464.3 463.6 463.0 462.4 461.8 ...
141
142
143
144
145 463.0 462.4 461.8 461.2 460.5 459.9 459.3 458.6 ...
146
147
148
149
...
Thanks for your attention.
microsoft-excel
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
add a comment |
I am creating an excel sheet out from the sounding table book of a fuel storage tank where the volume values were given for every fifth centimeter. I have to populate the empty cells with the interpolation values to use VLookUp function afterwards in order to get volume as a function of sounding (first column) and trim (x axis). I managed to insert four empty lines between each given values. Then I fill the cells in between by interpolating the values of upper and lower rows. Then I copy the interpolation range and paste it to the empty range. This is repeated hundreds of times. So I wonder if there could be an easier way as my total number of tanks is about 20 and the depth (y axis) is about 10000 cm. The problem is that if I copy and paste only a formula the empty destination cell is still overwritten to empty value. Otherwise I could reuse the formulas from the first tank.
...
...
125 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
126 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
127 474.7 474.1 473.5 472.9 472.3 471.6 471.0 470.4 ...
128 473.9 473.3 472.8 472.1 471.5 470.9 470.3 469.6 ...
129 473.2 472.6 472.0 471.3 470.7 470.1 469.5 468.8 ...
130 472.4 471.8 471.2 470.6 469.9 469.3 468.7 468.0 ...
131
132
133
134
135 469.3 468.7 468.0 467.4 466.8 466.2 465.5 464.9 ...
136
137
138
139
140 466.1 465.5 464.9 464.3 463.6 463.0 462.4 461.8 ...
141
142
143
144
145 463.0 462.4 461.8 461.2 460.5 459.9 459.3 458.6 ...
146
147
148
149
...
Thanks for your attention.
microsoft-excel
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
1
It's not clear what you are trying to do. Can you edit your question to explain in more detail.
– Blackwood
Nov 14 '17 at 14:07
Amended some details
– off-signer
Nov 14 '17 at 15:00
And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job..
– off-signer
10 hours ago
I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking?
– Scott
7 hours ago
add a comment |
I am creating an excel sheet out from the sounding table book of a fuel storage tank where the volume values were given for every fifth centimeter. I have to populate the empty cells with the interpolation values to use VLookUp function afterwards in order to get volume as a function of sounding (first column) and trim (x axis). I managed to insert four empty lines between each given values. Then I fill the cells in between by interpolating the values of upper and lower rows. Then I copy the interpolation range and paste it to the empty range. This is repeated hundreds of times. So I wonder if there could be an easier way as my total number of tanks is about 20 and the depth (y axis) is about 10000 cm. The problem is that if I copy and paste only a formula the empty destination cell is still overwritten to empty value. Otherwise I could reuse the formulas from the first tank.
...
...
125 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
126 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
127 474.7 474.1 473.5 472.9 472.3 471.6 471.0 470.4 ...
128 473.9 473.3 472.8 472.1 471.5 470.9 470.3 469.6 ...
129 473.2 472.6 472.0 471.3 470.7 470.1 469.5 468.8 ...
130 472.4 471.8 471.2 470.6 469.9 469.3 468.7 468.0 ...
131
132
133
134
135 469.3 468.7 468.0 467.4 466.8 466.2 465.5 464.9 ...
136
137
138
139
140 466.1 465.5 464.9 464.3 463.6 463.0 462.4 461.8 ...
141
142
143
144
145 463.0 462.4 461.8 461.2 460.5 459.9 459.3 458.6 ...
146
147
148
149
...
Thanks for your attention.
microsoft-excel
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
I am creating an excel sheet out from the sounding table book of a fuel storage tank where the volume values were given for every fifth centimeter. I have to populate the empty cells with the interpolation values to use VLookUp function afterwards in order to get volume as a function of sounding (first column) and trim (x axis). I managed to insert four empty lines between each given values. Then I fill the cells in between by interpolating the values of upper and lower rows. Then I copy the interpolation range and paste it to the empty range. This is repeated hundreds of times. So I wonder if there could be an easier way as my total number of tanks is about 20 and the depth (y axis) is about 10000 cm. The problem is that if I copy and paste only a formula the empty destination cell is still overwritten to empty value. Otherwise I could reuse the formulas from the first tank.
...
...
125 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
126 475.4 474.9 474.3 473.7 473.1 472.4 471.8 471.2 ...
127 474.7 474.1 473.5 472.9 472.3 471.6 471.0 470.4 ...
128 473.9 473.3 472.8 472.1 471.5 470.9 470.3 469.6 ...
129 473.2 472.6 472.0 471.3 470.7 470.1 469.5 468.8 ...
130 472.4 471.8 471.2 470.6 469.9 469.3 468.7 468.0 ...
131
132
133
134
135 469.3 468.7 468.0 467.4 466.8 466.2 465.5 464.9 ...
136
137
138
139
140 466.1 465.5 464.9 464.3 463.6 463.0 462.4 461.8 ...
141
142
143
144
145 463.0 462.4 461.8 461.2 460.5 459.9 459.3 458.6 ...
146
147
148
149
...
Thanks for your attention.
microsoft-excel
microsoft-excel
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
edited Nov 14 '17 at 14:59
off-signer
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
asked Nov 14 '17 at 10:01
off-signeroff-signer
82
82
1
It's not clear what you are trying to do. Can you edit your question to explain in more detail.
– Blackwood
Nov 14 '17 at 14:07
Amended some details
– off-signer
Nov 14 '17 at 15:00
And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job..
– off-signer
10 hours ago
I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking?
– Scott
7 hours ago
add a comment |
1
It's not clear what you are trying to do. Can you edit your question to explain in more detail.
– Blackwood
Nov 14 '17 at 14:07
Amended some details
– off-signer
Nov 14 '17 at 15:00
And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job..
– off-signer
10 hours ago
I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking?
– Scott
7 hours ago
1
1
It's not clear what you are trying to do. Can you edit your question to explain in more detail.
– Blackwood
Nov 14 '17 at 14:07
It's not clear what you are trying to do. Can you edit your question to explain in more detail.
– Blackwood
Nov 14 '17 at 14:07
Amended some details
– off-signer
Nov 14 '17 at 15:00
Amended some details
– off-signer
Nov 14 '17 at 15:00
And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job..
– off-signer
10 hours ago
And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job..
– off-signer
10 hours ago
I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking?
– Scott
7 hours ago
I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking?
– Scott
7 hours ago
add a comment |
1 Answer
1
active
oldest
votes
Based on what best I understand your question, I suggest a solution primarily based on OFFSET function and two Helper Columns. This is done for a single column. You shall need to repeat the same for all the columns.
This solution involves recreating the entire column elsewhere thru OFFSET Formula and then you need to Copy-Paste Special --> Values back to original column.
Assuming your data is in Cells B1:B16. Now create two helper columns. A column to the left with repeating sequence 0 1 2 3 4. All you need to do is select this range and simply paste it across all the applicable rows below. The sequence shall repeat again in the pasted cells. Similarly a column to the right with repeating sequence 0 4 3 2 1.
Now in D1 put the following formula and drag it down up to the intended length of the column.
=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))
Now simply Copy-Paste --> Paste Special --> Values, Column D to elsewhere and rebuild your entire sheet in this fashion.
This way you do not need to manually copy the formula adjusting the end value cells again and again between the two end values.
Assumptions -
There are exact 4 rows gaps between all applicable cells.
The extrapolation is not based on any complex maths, a simple subtraction of equal volumes between the two end values. If you are using any complex maths, possibly you may need another solution.
Values are always in decreasing order from top to down.
Explore this solution and see if it suits you.
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
1
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1268191%2ffilling-down-a-formula-in-excel-to-a-sparse-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Based on what best I understand your question, I suggest a solution primarily based on OFFSET function and two Helper Columns. This is done for a single column. You shall need to repeat the same for all the columns.
This solution involves recreating the entire column elsewhere thru OFFSET Formula and then you need to Copy-Paste Special --> Values back to original column.
Assuming your data is in Cells B1:B16. Now create two helper columns. A column to the left with repeating sequence 0 1 2 3 4. All you need to do is select this range and simply paste it across all the applicable rows below. The sequence shall repeat again in the pasted cells. Similarly a column to the right with repeating sequence 0 4 3 2 1.
Now in D1 put the following formula and drag it down up to the intended length of the column.
=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))
Now simply Copy-Paste --> Paste Special --> Values, Column D to elsewhere and rebuild your entire sheet in this fashion.
This way you do not need to manually copy the formula adjusting the end value cells again and again between the two end values.
Assumptions -
There are exact 4 rows gaps between all applicable cells.
The extrapolation is not based on any complex maths, a simple subtraction of equal volumes between the two end values. If you are using any complex maths, possibly you may need another solution.
Values are always in decreasing order from top to down.
Explore this solution and see if it suits you.
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
1
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
add a comment |
Based on what best I understand your question, I suggest a solution primarily based on OFFSET function and two Helper Columns. This is done for a single column. You shall need to repeat the same for all the columns.
This solution involves recreating the entire column elsewhere thru OFFSET Formula and then you need to Copy-Paste Special --> Values back to original column.
Assuming your data is in Cells B1:B16. Now create two helper columns. A column to the left with repeating sequence 0 1 2 3 4. All you need to do is select this range and simply paste it across all the applicable rows below. The sequence shall repeat again in the pasted cells. Similarly a column to the right with repeating sequence 0 4 3 2 1.
Now in D1 put the following formula and drag it down up to the intended length of the column.
=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))
Now simply Copy-Paste --> Paste Special --> Values, Column D to elsewhere and rebuild your entire sheet in this fashion.
This way you do not need to manually copy the formula adjusting the end value cells again and again between the two end values.
Assumptions -
There are exact 4 rows gaps between all applicable cells.
The extrapolation is not based on any complex maths, a simple subtraction of equal volumes between the two end values. If you are using any complex maths, possibly you may need another solution.
Values are always in decreasing order from top to down.
Explore this solution and see if it suits you.
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
1
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
add a comment |
Based on what best I understand your question, I suggest a solution primarily based on OFFSET function and two Helper Columns. This is done for a single column. You shall need to repeat the same for all the columns.
This solution involves recreating the entire column elsewhere thru OFFSET Formula and then you need to Copy-Paste Special --> Values back to original column.
Assuming your data is in Cells B1:B16. Now create two helper columns. A column to the left with repeating sequence 0 1 2 3 4. All you need to do is select this range and simply paste it across all the applicable rows below. The sequence shall repeat again in the pasted cells. Similarly a column to the right with repeating sequence 0 4 3 2 1.
Now in D1 put the following formula and drag it down up to the intended length of the column.
=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))
Now simply Copy-Paste --> Paste Special --> Values, Column D to elsewhere and rebuild your entire sheet in this fashion.
This way you do not need to manually copy the formula adjusting the end value cells again and again between the two end values.
Assumptions -
There are exact 4 rows gaps between all applicable cells.
The extrapolation is not based on any complex maths, a simple subtraction of equal volumes between the two end values. If you are using any complex maths, possibly you may need another solution.
Values are always in decreasing order from top to down.
Explore this solution and see if it suits you.
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
Based on what best I understand your question, I suggest a solution primarily based on OFFSET function and two Helper Columns. This is done for a single column. You shall need to repeat the same for all the columns.
This solution involves recreating the entire column elsewhere thru OFFSET Formula and then you need to Copy-Paste Special --> Values back to original column.
Assuming your data is in Cells B1:B16. Now create two helper columns. A column to the left with repeating sequence 0 1 2 3 4. All you need to do is select this range and simply paste it across all the applicable rows below. The sequence shall repeat again in the pasted cells. Similarly a column to the right with repeating sequence 0 4 3 2 1.
Now in D1 put the following formula and drag it down up to the intended length of the column.
=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))
Now simply Copy-Paste --> Paste Special --> Values, Column D to elsewhere and rebuild your entire sheet in this fashion.
This way you do not need to manually copy the formula adjusting the end value cells again and again between the two end values.
Assumptions -
There are exact 4 rows gaps between all applicable cells.
The extrapolation is not based on any complex maths, a simple subtraction of equal volumes between the two end values. If you are using any complex maths, possibly you may need another solution.
Values are always in decreasing order from top to down.
Explore this solution and see if it suits you.
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
edited Nov 14 '17 at 18:40
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
answered Nov 14 '17 at 18:31
patkimpatkim
3,2542725
3,2542725
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
1
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
add a comment |
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
1
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort.
– off-signer
Nov 16 '17 at 11:01
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle.
– patkim
Nov 16 '17 at 11:05
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-$O6,0)+$O6*(OFFSET(H6,$P6,0)-OFFSET(H6,-$O6,0))/5)"
– off-signer
Nov 16 '17 at 11:12
1
1
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though.
– patkim
Nov 16 '17 at 12:05
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1268191%2ffilling-down-a-formula-in-excel-to-a-sparse-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
It's not clear what you are trying to do. Can you edit your question to explain in more detail.
– Blackwood
Nov 14 '17 at 14:07
Amended some details
– off-signer
Nov 14 '17 at 15:00
And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job..
– off-signer
10 hours ago
I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking?
– Scott
7 hours ago