Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel formula question. I am having problems with adding and subtracting numbers in a complex formula.?
Currently I have a simple formula that is subtraction =sum(A1-A2). Example 100.0 - 99.0=1.0. This corresponds with time. A reading at 8:00AM (A1) and 9:00AM (A2). My problem is I need to add more numbers into the equation. 8:00AM (A1), 8:15AM (A2), 8:30AM (A3), 8:45AM (A4), 9:00AM (A5). So it would look like 100.0 - 99.7 - 99.5 - 99.3 - 99.0. I still need the final answer to be 1.0 but doing the normal equation the answer would be -297.5. The reason why I have to have the additional times is because the number could go up (Example- 100.0 - 99.7 - 99.5 - 99.8 - 100.5 = -0.5 (the negative makes sense to me and my work.) I will never know when the numbers will be going up or down so I can not simply put a minus or addition symbol in at a certain cell. The numbers will not be put in manually. Excel will be automatically pulling these numbers at the specified times from another piece of software
1 Answer
- Greg GLv 76 years ago
So it appears that you need to have a formula that subtracts the last entry (whether it's in A2, A3, A4 or A5) from the first entry in A1.
If this is the case, use this in B1 (or any other column besides A)
=A1-INDEX(A:A, COUNTA(A:A), 1)
This also works:
=A1-INDEX(A:A, MATCH(9.99999999999999E+307, A:A))
Either INDEX formula will return the value from the last entry in the range A:A.
You can change that to only include a certain range, such as A2:A5, if you prefer that.
