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,""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marie1uk" wrote:
>
> 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
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=546141
>
>

[Marie1uk]

Thanks 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 ?

NB: A little exclamation mark next to the cell returning the error says 'A value used in this formula is of thr wrong data type'

[Max]

You're welcome, Marie1uk !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marie1uk" wrote:
>
> Thanks Max

[Max]

"Marie1uk" wrote:
> .. 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:

"Marie1uk" wrote:
>
> 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>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---