Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Marie1uk
 
Posts: n/a
Default Conditional string calculations


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Conditional string calculations

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Marie1uk
 
Posts: n/a
Default Conditional string calculations


Thanks Max


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=546141

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Conditional string calculations

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

Thanks Max

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Conditional string calculations

"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
---
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Property Let: assign return value of Double when passing String Tetsuya Oguma Excel Discussion (Misc queries) 1 March 3rd 06 09:01 AM
Conditional Formatting when inserting a row zahoulik Excel Worksheet Functions 2 January 7th 06 04:01 PM
Looking up a string of text within a string of text tobriant Excel Worksheet Functions 4 September 20th 05 06:59 PM
Auto convert an alphanumeric string (CIS9638S) to numbers only? Gary L Brown Excel Worksheet Functions 1 September 7th 05 01:17 AM
Updating master workbook from source that may/may not exist [email protected] Excel Worksheet Functions 20 April 7th 05 03:37 PM


All times are GMT +1. The time now is 12:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"