"Help with Formula/Decision"

  • Thread starter Thread starter Jim Badinger
  • Start date Start date
J

Jim Badinger

Greetings,
I am in need of some help with this form (See Below).
Notice that I have started the first function as B2=IF
(A2,D2+A2,"N/A") to get the total I wanted here. However I
can't figure out how to do the remainder. I wish for Cell
B2 to become a "temporary" constant, and the following C
Column data entered to be subtracted from it as you can
see in the example I provided. For example the data in
Cell C3 was subtracted from Cell B2 and displayed in the
corresponding D Cell; in this case D3. I wish for this to
continue right down the C & D Columns until an A Column
(in this case Cell A5) entry causes the "TopofCase" or B
Column to display a "new" temporary constant. This will
occur by a new A Column entry and it will cause the
corresponding B Cell to show this new value by taking the
corresponding D Cell value (in this example we see Cell D5
is 128.39) and adding the "new" A Column entry to it. As
the example shows Cell D5 was added to Cell A5 and the
answer or value displayed in Cell B5. This new "temporary"
constant is used as the previous "temporary" constant
until another "TopOfCase" is needed or the last C Column
entry ends the process.




A B C D
1 ADD TOPOFCASE SUBTRACT GROUNDING
2 5.10 130.10 125.00
3 4.50 125.60
4 3.25 126.85
5 7.34 135.73 1.71 128.39
6 2.59 133.14
7 2.32 133.41


I hope this text display helps with some visual
reference...Thanks

Signed,
Jim Badinger

..
 
1 ADD TOPOFCASE SUBTRACT GROUNDING
2 5.1 130.10 125.00
3 130.10 4.50 125.60
4 130.10 3.25 126.85
5 7.34 135.73 1.71 128.39
6 135.73 2.59 133.14
7 135.73 2.32 133.41

I changed the formula in b2 to =IF(A2,A2+D2,B1) and
copied down, the formula in d3 =B2-C3 and copied down.

Lance
PS: You could use conditional formatting ie =COUNTIF
($B$2:B2,B2)>1 and change the font to white to eliminate
the multiple 130.10 and the multiple 137.73 if desired.
 
Lance, great solution. I had tried something like this but
did not know how to "kill" the repeat numbers. Changing
the format to white was perfect...

Thanks Again!
 
Back
Top