# Combining two functions

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

3. ## Re: Combining two functions

Hi Spencer,

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

4. ## 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.  Register To Reply

5. ## Re: Combining two functions  Register To Reply

6. ## Re: Combining two functions

look at below this message box,
there's "Go advanced" button click it.
scroll down and you'll see "manage attachment" button..  Register To Reply

7. ## Re: Combining two functions

Its the forum ID and password right?  Register To Reply

8. ## 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.  Register To Reply

9. ## Re: Combining two functions

Any solutions?  Register To Reply

10. ## Re: Combining two functions

Can you explain step by step what you trying to do?  Register To Reply

11. ## 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?  Register To Reply

12. ## 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"  Register To Reply

13. ## 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  Register To Reply

14. ## 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)  Register To Reply

15. ## Re: Combining two functions

Here you go this is the updated file  Register To Reply

16. ## 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 Originally Posted by zbor 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)  Register To Reply

17. ## Re: Combining two functions Originally Posted by jordache_keith 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.  Register To Reply

18. ## 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.  Register To Reply

19. ## Re: Combining two functions Originally Posted by zbor 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  Register To Reply

20. ## 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))  Register To Reply

21. ## 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?  Register To Reply

22. ## Re: Combining two functions

see upper formula  Register To Reply

23. ## 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?  Register To Reply

24. ## 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.  Register To Reply

25. ## Re: Combining two functions

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