Automatic Answer

[Zygy]

Is there a way of permanently arranging in col. H that the result of the
amount in col. D plus amount in col.E less the amount in col.F is
automatically shown once the entries in cols. D, E & F have been made. I am
tired of using copy and paste system!

 

[Max]

One way ..
Put in H2:
=IF(COUNTBLANK(D2:F2)<>0,"",SUM(D2:E2)-F2)
Copy H2 down to cover the max expected extent of data, say to H200 ?
Col H will appear "blank" until entries in cols D, E & F have been made

 

[Max]

Copy H2 down to cover the max expected extent of data, say to H200 ?
To copy down, just drag the fill handle (the solid black square at the
bottom right corner of H2) down to H200

---

 

[Zygy]

Thank you for the reply. When I entered your formula in H2 and clicked OK, I
was presented with another Window titled "Function Arguments" and any of the
alternatives offered in "Help on this Function" appeared inappropriate for
my cell.

How do I proceed after entering your formula in H2, to get the answer in
pund sterling, having enter in pund sterling cells D2,E2 & F2? Thank you.

 

[Gord Dibben]

When you say you "entered the formula in H2 and clicked OK" did you mean you hit
the ENTER key which is what you should do?

Otherwise, what did you click OK on?

After entering Max's formula in H2, copy it down H a bunch of cells.

Start by plugging numbers into D2,E2 and F2 followed by numbers into other cells
down D, E and F columns.


Gord Dibben MS Excel MVP

 

[Zygy]

To answer your question first- The Function Arguments Window, which I get to
enter the Formula has a OK button on it, which I always used with perfect
results. I tried again and I entered the formula copied and pasted below,
but now I get a notice stating that "your formula is missing Parenthesis..."
=SUM IF(COUNTBLANK(D:F2)<>0,"",SUM(D2:E2)-F2)
What is your comment on that?

 

[Gord Dibben]

These are my comments.............................

To start with, I would just copy Max's formula into a cell without bothering
with the Function Wizard.

Secondly, I don't know how you got the extra "SUM" in Max's formula.

If you copy directly from the post, there is less chance of making a typo, which
you seem to have done during your journey to the Function Wizard.

=IF(COUNTBLANK(D2:F2)<>0,"",SUM(D2:E2)-F2) as posted by Max.


Gord

 

[Max]

Appreciate the help, Gord. Thanks!

Zygy: Hope you've got it to work in col H since

As for your side Q in your earlier reply:

How do I proceed after entering your formula in H2,
to get the answer in pound sterling,
having enter in pound sterling cells D2,E2 & F2?

Just format H2 as currency, Symbol: £ English (United Kingdom)
before you copy H2 down. That should do it nicely.

---

 

[Zygy]

Thank you both for the help! I have got it now.

Appreciate the help, Gord. Thanks!

Zygy: Hope you've got it to work in col H since

As for your side Q in your earlier reply:

Just format H2 as currency, Symbol: £ English (United Kingdom)
before you copy H2 down. That should do it nicely.

 

[Max]

Glad to hear that !
Thanks for feeding back ..