+ Reply to Thread
Results 1 to 25 of 25

Combining two functions

  1. #1
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Cool Combining two functions

    Hi Guys,

    Is it possible to combine this 2 formulas?

    EG(SingaporePH)

    =IF(R2="Singapore",IF(AND(B2-PH!$A$2<1, PH!$A$2-O2<1),1,0)+IF(AND(B2-PH!$A$3<1,PH!$A$3-O2<1),1,0)+IF(AND(B2-PH!$A$4<1,PH!$A$4-O2<1),1,0)+IF(AND(B2-PH!$A$5<1,PH!$A$5-O2<1),1,0)+IF(AND(B2-PH!$A$6<1,PH!$A$6-O2<1),1,0)+IF(AND(B2-PH!$A$7<1,PH!$A$7-O2<1),1,0)+IF(AND(B2-PH!$A$8<1,PH!$A$8-O2<1),2,0)
    +IF(AND(B2-PH!$A$9<1,PH!$A$9-O2<1),1,0))

    EG(GermanyPH)

    =IF(R2="Germany",IF(AND(B2-PH!$D$2<1,PH!$D$2-O2<1),1,0)+IF(AND(B2-PH!$D$3<1,PH!$D$3-O2<1),2,0)+IF(AND(B2-PH!$D$4<1,PH!$D$4-O2<1),1,0)+IF(AND(B2-PH!$D$5<1,PH!$D$5-O2<1),1,0)+IF(AND(B2-PH!$D$6<1,PH!$D$6-O2<1),1,0)+IF(AND(B2-PH!$D$7<1,PH!$A$7-O2<1),1,0)+IF(AND(B2-PH!$D$8<1,PH!$D$8-O2<1),2,0)
    +IF(AND(B2-PH!$D$9<1,PH!$D$9-O2<1),1,0)+IF(AND(B2-PH!$D$10<1,PH!$D$10-O2<1),2,0)+IF(AND(B2-PH!$D$11<1,PH!$D$11-O2<1),1,0)+IF(AND(B2-PH!$D$12<1,PH!$D$12-O2<1),1,0)+IF(AND(B2-PH!$D$13<1,PH!$D$13-O2<1),1,0)+IF(AND(B2-PH!$D$14<1,PH!$D$14-O2<1),1,0)+IF(AND(B2-PH!$D$15<1,PH!$D$15-O2<1),1,0)+IF(AND(B2-PH!$D$16<1,PH!$D$16-O2<1),1,0))

    Thanks alot

    Jordache

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Combining two functions

    Hi Jordache,

    It would be far easier to answer your question if we could see the formulas in action.
    Could you post an example workbook?

    S.

  3. #3
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Hi Spencer,

    I am having difficulties uploading my document. Do u have an email that i can forward you

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    Probably is bigger than 1MB.

    Reduce it and leave only parts important for you solutions.
    Make sure to remove all private data.

  5. #5
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    The file is only about 500kb though. They request for a login and password upon uploading and it hangs there after

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Combining two functions

    are you uploading here??

    look at below this message box,
    there's "Go advanced" button click it.
    scroll down and you'll see "manage attachment" button..
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  7. #7
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    The file is only about 500kb. Upon uploading it always hangs when asked for a login/password.
    Its the forum ID and password right?

  8. #8
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Here is the file..if you realize in the raw data there is 2 columns X and Y..the formulas come from there..i am referencing this values from a PH tab where i date down all the public holidays..Instead of having multiple columns in the raw data if i were to add on more countries. Is it possible to have just one column where i can state the conditions.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Any solutions?

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    Can you explain step by step what you trying to do?

  11. #11
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    As you can see from the raw data tab..i have have columns X-AD which calculates the number of public holidays
    that falls between the create date(column B) and the actual res date(Column 0). My concern is really how to combine all those fields
    into a single column. The PH tab is where i am referencing all the values from.

    Basically i would like to combine this 2 statements:

    Statement 1
    =IF(R2="Singapore",IF(AND(B2-PH!$A$2<1,PH!$A$2-O2<1),1,0)+IF(AND(B2-PH!$A$3<1,PH!$A$3-O2<1),1,0))

    AND

    Statement 2
    =IF(R2="Malaysia",IF(AND(B2-PH!$D$2<1,PH!$D$2-O2<1),1,0)+IF(AND(B2-PH!$D$3<1,PH!$D$3-O2<1),2,0))

    instead of having this 2 statements in 2 different columns, it is possible to join this if else statements together into one column?
    Last edited by jordache_keith; 05-11-2012 at 03:22 AM.

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    Here I've try to do something...

    But I've changed few things:

    1. If you use dates then you must have cells formated as dates (So I've reformat cells in B and O column but I suggest you to do that for all cells with dates)

    2. I rearanged PH sheet so all holidays are in the same column now. Insert more holidays at the end.

    3. For counting holidays insert country names in X, Y and Z column.
    If you put for example 10.6.2011 as a holiday in Singapore and Malysia in Z8 you will get result:
    7 (if holidays is not defined)
    6 (if at least one country is defined)
    6 (if both countries are defined).

    To enter countries in those cells must be entered as "PH country_name"

  13. #13
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Thank you.

    But the problem of doing this you would still create more than one column to calculate the number of public holidays for a country. What i would like to do is to just have one column "X" which counts all the holidays be it whether its Singapore,Malaysia,USA and etc. I have tried something out on a new excel worksheet, maybe u will have a clearer picture of what i am trying to do from seeing this..thanks

  14. #14
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    if you calculating ALL holidays with no matter which country then don't use criteria for holidays:

    =NETWORKDAYS(B2,O2,PH!$A$2:$A$10000)

  15. #15
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Here you go this is the updated file
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Yes but according to my current formula, it calculates whether the holidays last over 2,3 so on days..
    thats why i am using this formula to calculate the number of days this public holiday last



    Quote Originally Posted by zbor View Post
    if you calculating ALL holidays with no matter which country then don't use criteria for holidays:

    =NETWORKDAYS(B2,O2,PH!$A$2:$A$10000)

  17. #17
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Quote Originally Posted by jordache_keith View Post
    Yes but according to my current formula, it calculates whether the holidays last over 2,3 so on days..
    thats why i am using this formula to calculate the number of days this public holiday last
    Sorry for my misunderstanding, i would need to know the number of holidays from that specific country in row R.

  18. #18
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    if some holiday last for more than one day: For example Christmas on 25th and 26th) then you can write BOTH dates in a table.

  19. #19
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Quote Originally Posted by zbor View Post
    if you calculating ALL holidays with no matter which country then don't use criteria for holidays:

    =NETWORKDAYS(B2,O2,PH!$A$2:$A$10000)
    Hi Sorry Zbor, i would need to know which country it is, from the input of row R

  20. #20
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    Here, try this and comfirm with ctrl+shift+enter (not just enter):

    =NETWORKDAYS(B2,O2,IF(PH!$C$2:$C$10000=$R2,PH!$A$2:$A$10000,0))

  21. #21
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    =NETWORKDAYS(B2,O2,IF(PH!$C$2:$C$10000=SUBSTITUTE('Raw Data'!X$1:Z$1,"PH ",""),PH!$A$2:$A$10000,0))

    But how does this formula read my inputed data in Column "R" to decide which set of public holiday to choose from in the PH tab?

  22. #22
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    see upper formula

  23. #23
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Im sorry the updating of the thread on my computer is very laggy..i have tried to input your formula but some how i dont think it is accurate.
    There is negative signs on that column and for example in row 7 the create date is 2 june 2012 and the actual res date is 7 July 2012..according to the calculation its 26 days..the figure just dont seem to be right..is it calculating all countries PH during that period of time or is it stating only that specific country in Row R?

  24. #24
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Combining two functions

    Dates might be changed comparing to your original table.
    In this case it was 2011.

    Now I put: 2.6.2012 to 7.7.2012 and it gives (in column Z) 25 days (excluding 10.6.2012).
    In AA column is date difference with weekends but without holidays.

    Change few days and countries and test it further.

  25. #25
    Registered User
    Join Date
    05-10-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Combining two functions

    Thanks i will give it a try and get back to u..thanks for all the help

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1