Subtraction problem - finding the difference

  • Thread starter Thread starter nd2no
  • Start date Start date
N

nd2no

(I can't figure out how to insert blank space in a post)

......A..........B
1....0..........0
2....100......0
3....0..........0
4....0..........0
5....500......400
6....730......230

Is there a way to get B to find the difference between numbers in A
returning a positive value - as in the example?

:confused:
:
 
Put this formula in B2 and copy down

=IF(A2<>0,A2-INDEX(A$1:A$100,MAX(ROW(A$1:A1)*(A$1:A1<>0))),0)

it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Also, assuming that the data is in A2:A7, with A1 housing a label like
Nums...

In B2 enter & copy down:

=IF(COUNT($A$1:A2)>=2,A2-LOOKUP(9.99999999999999E+307,$A$1:A1),"")
 
This stuff is fascinating! The following works great, except for th
first result:
.........Q...............R....
2....Mileage........Miles
3.......0.............(blank)
4.......0................0
5.......0................0
6.......0................0
7.......0................0
8.......0................0
9...825427.......#VALUE!
etc.
etc.
19..826539.......1112

Wherein:
R4 is {=IF(Q4<>0,Q4-INDEX(Q$1:Q$100,MAX(ROW(Q$1:Q3)*(Q$1:Q3<>0))),0)}
R5 is {=IF(Q5<>0,Q5-INDEX(Q$1:Q$100,MAX(ROW(Q$1:Q4)*(Q$1:Q4<>0))),0)}

Etc. (copied down)

R9 is {=IF(Q9<>0,Q9-INDEX(Q$1:Q$100,MAX(ROW(Q$1:Q8)*(Q$1:Q8<>0))),0)}
R19 i
{=IF(Q19<>0,Q19-INDEX(Q$1:Q$100,MAX(ROW(Q$1:Q18)*(Q$1:Q18<>0))),0)}

I'm so new at this.... Is there a way to fix the formula so that th
first result doesn't return "#VALUE!"?

Thanks for your time
Sherr
 
I'm such a novice at this. I don't know how to use the lookup formula.
I really like what Bob Phillips gave me, and can (happily) live wit
what I've got. I was just wondering if there was a way to manipulat
the formula so that the first value would work right. But I appreciat
you taking the time to look at it
 
Hi,

------A--------B---------
1 Value Diff.
2 0 (Formula)
3 100
4 0
5 0
6 500
7 730
-------------------------

Put this formula as an array formula in B2

1) Select a cell B2

2) Build the formula using the normal formula entry techniques.
(Do not type braces around the formula)

=IF(ROWS(A$2:A2)=1,A2,IF(A2<>0,A2-N(INDEX(A$1:A$100,
MAX(ROW(A$1:A1)*(A$1:A1<>0)))),0))

or

=IF(ROWS(A$2:A2)=1,A2,IF(A2=0,0,A2-N(INDIRECT("A"&
MAX(IF(A$1:A1<>0,ROW(A$1:A1),""))))))

3) Press CTRL+SHIFT+ENTER
(Microsoft Excel adds the braces when you enter the formula
as an array formula.)

4) then drag and fill down.


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--
 
Back
Top