Grab the last values

jltjr75

New Member
Joined
Oct 15, 2013
Messages
14
I want the final total between both accounts added when I update the numbers quarterly. For example D9+I9 = B1
Where I will have the problem is when I add the next quarter to row 10 (then row 11,12,13...) Is there a formula that can grab the last values (D10+I10) and = it in B1?
I would like this to continue as I add future quarters automatically.
excel-001.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
One way...

=LOOKUP(9E+99,D:D)+LOOKUP(9E+99,I:I)

although it would be more efficient if you could establish a small maximum range such as...

=LOOKUP(9E+99,D1:D999)+LOOKUP(9E+99,I1:I999)
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Excel Formula:
=LOOKUP(9.99999999999999E+307,D5:D200)+LOOKUP(9.99999999999999E+307,I5:I200)

although it would be more efficient if you could establish a small maximum range such as...

=LOOKUP(9E+99,D1:D999)+LOOKUP(9E+99,I1:I999)

Edit: I used 200, I figured since we're doing Quarterly, that's nearly 50 years worth of rows :)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
@jtakw,

Our formulas are the same except that you opted to use the "correct" number (9.99999999999999E+307) instead of my number (9E+99). I hate typing long formulas and figured that since the OP was working with money values, a number with 99 digits would always be larger than any dollar amount the OP would have. As for using 200 or 999... I find typing the same digit easier than using differing digits... once we assume three digits, I almost automatically use repeated 9s.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I pointed out using 200 strictly for the humor factor, nearly 50 years worth, but OP can use whatever he wants.
As for the dollar amount, he "could" be Jeff Bezos or Elon Musk in disguise :oops:
 

jltjr75

New Member
Joined
Oct 15, 2013
Messages
14
Thank you both. I've studied the formulas and they seem easy enough. I'll add them to my toolbelt. I did change numbers for this forum but I'm no Jeff Bezos or Elon Musk :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top