+ Reply to Thread
Results 1 to 15 of 15

Excel if function used for different scenarios

  1. 03-17-2011, 10:36 AM #1
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Excel if function used for different scenarios

    Hi I was hoping someone could help me out. I have attached a spreadsheet that has different scenarios that I need an if function to work with. I have detailed it on the spreadsheet exactly what I need to achieve and I would be so gradeful if someone could help me.

    Thank you

    frankie
    Attached Files Attached Files
    Last edited by NBVC; 03-17-2011 at 04:25 PM.
    Reply With QuoteRegister To Reply
  2. 03-17-2011, 10:46 AM #2
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel if function used for different scenarios

    Does this work for all scenarios?

    In D27:

    =IF(SUM(COUNTIF(C27:C28,{"AL","PL"})),LEFT(C26,5)-IF(C27="PL",IF(C28="AL",D28,0),0),"")
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 03-17-2011, 11:33 AM #3
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    thanks for replying to me, I have tried this but it doesn't give me the answer I need. It should be giving me the difference between line 1 (first5characters) and line 3 d28.

    the answer should be 3.
    Reply With QuoteRegister To Reply
  4. 03-17-2011, 11:38 AM #4
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel if function used for different scenarios

    That happens only when PL is selected in first row (C27) as per your comments... is that wrong?
    Reply With QuoteRegister To Reply
  5. 03-17-2011, 11:40 AM #5
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    Sorry I get what you mean now, that's perfect. was thinking of it slightly differently. Thanks a million for this. I knew what I wanted to achieve but just couldn't get it.

    Frankie.
    Reply With QuoteRegister To Reply
  6. 03-17-2011, 11:43 AM #6
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel if function used for different scenarios

    You are welcome. Happy St. Patrick's day
    Reply With QuoteRegister To Reply
  7. 03-17-2011, 11:46 AM #7
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    Happy St. Patrick's Day!!!!!!!
    Reply With QuoteRegister To Reply
  8. 03-17-2011, 01:27 PM #8
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    Sorry I'm not sure if I'm allowed ask this but is it possible to have the formula to include other choices in the drop down. is it possible to leave it open to whatever is put in the line 3 and leave line 2 always at AL.

    My apologies if I need to post this again, please let me know.
    Reply With QuoteRegister To Reply
  9. 03-17-2011, 01:35 PM #9
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel if function used for different scenarios

    You can re-open the thread based on it being directly related...

    I however, am not sure I understand what you mean. Can you elaborate and how is this different than what we came up with earlier?
    Reply With QuoteRegister To Reply
  10. 03-17-2011, 01:43 PM #10
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    Thanks, I have never used this forum before until now. Ok, there are lines 1,2 and 3. 1 being the hrs, start/end time. Line 2 and Line 3 will be absence lines and based on whichever absence is entered into line 3 I need Line 2 to do the sum to give the difference between Line 1 and 3. So If I use only AL in the drop down in line 2, then use different drop down names on Line 3, I still need Line 2 to be either blank if AL is not used or to give me the difference regardless of what is used in line 3. What you came up earlier with works fine if you alternate between the two lines but I really need to leave Line 2 for AL. I'm sorry I didn't explain this better. Frankie.
    Reply With QuoteRegister To Reply
  11. 03-17-2011, 01:51 PM #11
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel if function used for different scenarios

    I am still a bit confused. Is it possible for you to post another workbook like you did before and show different scenarios, and include expected results for each scenario (with brief explanation why). That would help, I think.
    Reply With QuoteRegister To Reply
  12. 03-17-2011, 01:53 PM #12
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    I will post another workbook. Thank you.
    Reply With QuoteRegister To Reply
  13. 03-17-2011, 02:25 PM #13
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Excel if function used for different scenarios

    I have attached the second workbook, hope you can understand it.

    Frankie.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 03-17-2011, 02:39 PM #14
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Excel if function used for different scenarios

    Does this do it in D4?

    =IF(C4="AL",LEFT(C3,5)-IF(C5="",0,D5),"")
    Reply With QuoteRegister To Reply
  15. 03-17-2011, 02:48 PM #15
    frankiebrooke is offline
    Registered User
    Join Date
    11-02-2010
    Location
    donegal, ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Smile Re: Excel if function used for different scenarios

    That done the trick Thank you again....very very very very much............

    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1