Conditional string calculations

[Marie1uk]

Hi,

I am trying to devise a form that will calculate travel expenses. Users
will input their mileometer readings (the start and end readings) in L4
and N4 respectively.In cell G4 users will input their reason for
travel, either 'Travel between sites' or another reason.

If the string in G4 is 'Travel between sites' then the formula (N4-L4)
should return the result in P4 (entitled Business Miles). If the string
in G4 is anything else the result should be returned in T4 (Home to
base). I do not want 0 values for blank values in L4 and N4 to show on
the form so I have got:

=IF(ISBLANK(N4),"",N4-L4)

Can anyone suggest a way of achieving both goals, ie keeping blank
cells empty AND checking the string in G4 to return a value (N4-L4) in
either P4 or T4?

Any suggestions gladly received

 

[Max]

Try ..

In P4:
=IF(ISBLANK(N4),"",IF(G4="Travel between sites",N4-L4,""))

In T4:
=IF(ISBLANK(N4),"",IF(G4<>"Travel between sites",N4-L4,""))

 

[Marie1uk]

Thanks Max

 

[Max]

You're welcome, Marie1uk !

 

[Max]

.. but I have a problem. The formula in T4
=IF(ISBLANK(N4),"",IF(G4<>"Travel between sites",N4-L4,""))
works well but if the user deletes the string in G4 the error #VALUE! occurs.
Any way to stop this happening ?

Think the #VALUE! is resulting from either N4 and/or L4 having text instead
of numbers, rather than G4 being cleared

Perhaps try this revised version which now traps for text in either L4 or N4:
=IF(OR(ISTEXT(L4),ISTEXT(N4)),"",IF(G4<>"Travel between sites",N4-L4,""))

Btw, please do not edit your post in excelforum (albeit this seems to be
allowed). Your edits won't go through to the excel newsgroup. For info, I had
read your earlier reply (and responded <g>) which was:

Thanks Max

If you want / need to further clarify subsequent to posting, just do it as a
reply to your own, earlier post. In that way, your clarifications will carry
through and continue to be visible to responders / readers in the excel
newsgroups.

(I happened to drop by excelforum and "detected' your add-on query above <g>)