I am trying to make an excel formula that subtracts the 2nd to last number in a column from the last number in the column The Next CEO of Stack OverflowGet content of a cell given the row and column numbersShortcut to Apply a Formula to an Entire Column in ExcelExcel formula to sum row until blanks in columnsExcel formula for counting row number starting belowExcel Formula - How to extract date from a week with custom start date?How to ignore or copy a part of formula in EXCEL from one column to another?Excel formula to add numbers stored as textExcel - my formula needs to incorporate the last filled cell in columnReturn last value in a row - do not consider formula as value - Excel 2016Adding the last row number to an excel formula in VBA
Could you use a laser beam as a modulated carrier wave for radio signal?
How to coordinate airplane tickets?
Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact
Can I hook these wires up to find the connection to a dead outlet?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Could a dragon use hot air to help it take off?
Oldie but Goldie
Read/write a pipe-delimited file line by line with some simple text manipulation
Do I need to write [sic] when including a quotation with a number less than 10 that isn't written out?
Why doesn't Shulchan Aruch include the laws of destroying fruit trees?
Gödel's incompleteness theorems - what are the religious implications?
MT "will strike" & LXX "will watch carefully" (Gen 3:15)?
A hang glider, sudden unexpected lift to 25,000 feet altitude, what could do this?
How can I separate the number from the unit in argument?
logical reads on global temp table, but not on session-level temp table
Car headlights in a world without electricity
Is it reasonable to ask other researchers to send me their previous grant applications?
Can Sri Krishna be called 'a person'?
Calculate the Mean mean of two numbers
Is the 21st century's idea of "freedom of speech" based on precedent?
Why does the freezing point matter when picking cooler ice packs?
Is this a new Fibonacci Identity?
Identify and count spells (Distinctive events within each group)
How can a day be of 24 hours?
I am trying to make an excel formula that subtracts the 2nd to last number in a column from the last number in the column
The Next CEO of Stack OverflowGet content of a cell given the row and column numbersShortcut to Apply a Formula to an Entire Column in ExcelExcel formula to sum row until blanks in columnsExcel formula for counting row number starting belowExcel Formula - How to extract date from a week with custom start date?How to ignore or copy a part of formula in EXCEL from one column to another?Excel formula to add numbers stored as textExcel - my formula needs to incorporate the last filled cell in columnReturn last value in a row - do not consider formula as value - Excel 2016Adding the last row number to an excel formula in VBA
For example H8 -H6 with H7 being blank. Easy =H8-H6. I get that but then there will be rows added and H may have a lot of blank cells but then a number is entered on H15. Now I want H15-H8. Is there an easy way to do this without having to re-do the simple formulas every time?
excel-formula
edited Mar 21 at 20:02
Scott Craner
93.3k82652
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
add a comment |
For example H8 -H6 with H7 being blank. Easy =H8-H6. I get that but then there will be rows added and H may have a lot of blank cells but then a number is entered on H15. Now I want H15-H8. Is there an easy way to do this without having to re-do the simple formulas every time?
excel-formula
edited Mar 21 at 20:02
Scott Craner
93.3k82652
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
Nevermind! It worked!!!! THANK YOU!!!!!!!!!
– Helpmeplease
Mar 21 at 20:17
add a comment |
For example H8 -H6 with H7 being blank. Easy =H8-H6. I get that but then there will be rows added and H may have a lot of blank cells but then a number is entered on H15. Now I want H15-H8. Is there an easy way to do this without having to re-do the simple formulas every time?
excel-formula
edited Mar 21 at 20:02
Scott Craner
93.3k82652
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
For example H8 -H6 with H7 being blank. Easy =H8-H6. I get that but then there will be rows added and H may have a lot of blank cells but then a number is entered on H15. Now I want H15-H8. Is there an easy way to do this without having to re-do the simple formulas every time?
excel-formula
excel-formula
edited Mar 21 at 20:02
Scott Craner
93.3k82652
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
edited Mar 21 at 20:02
Scott Craner
93.3k82652
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
edited Mar 21 at 20:02
Scott Craner
93.3k82652
edited Mar 21 at 20:02
Scott Craner
93.3k82652
edited Mar 21 at 20:02
Scott Craner
93.3k82652
93.3k82652
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
asked Mar 21 at 19:48
HelpmepleaseHelpmeplease
32
32
Nevermind! It worked!!!! THANK YOU!!!!!!!!!
– Helpmeplease
Mar 21 at 20:17
add a comment |
Nevermind! It worked!!!! THANK YOU!!!!!!!!!
– Helpmeplease
Mar 21 at 20:17
Nevermind! It worked!!!! THANK YOU!!!!!!!!!
– Helpmeplease
Mar 21 at 20:17
Nevermind! It worked!!!! THANK YOU!!!!!!!!!
– Helpmeplease
Mar 21 at 20:17
add a comment |
1 Answer
1
active
oldest
votes
Use INDEX/AGGREGATE:
=INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),1))-INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),2))
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
add a comment |
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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%2fstackoverflow.com%2fquestions%2f55288252%2fi-am-trying-to-make-an-excel-formula-that-subtracts-the-2nd-to-last-number-in-a%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
Use INDEX/AGGREGATE:
=INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),1))-INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),2))
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
add a comment |
Use INDEX/AGGREGATE:
=INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),1))-INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),2))
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
add a comment |
Use INDEX/AGGREGATE:
=INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),1))-INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),2))
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
Use INDEX/AGGREGATE:
=INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),1))-INDEX(H:H,AGGREGATE(14,7,ROW(INDEX(H:H,2):INDEX(H:H,ROW()-1))/(INDEX(H:H,2):INDEX(H:H,ROW()-1)<>""),2))
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
answered Mar 21 at 20:06
Scott CranerScott Craner
93.3k82652
93.3k82652
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f55288252%2fi-am-trying-to-make-an-excel-formula-that-subtracts-the-2nd-to-last-number-in-a%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
Nevermind! It worked!!!! THANK YOU!!!!!!!!!
– Helpmeplease
Mar 21 at 20:17