# 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

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.

3. ## Re: Combining two functions

Hi Spencer,

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

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.

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..

7. ## Re: Combining two functions

Its the forum ID and password right?

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.

9. ## Re: Combining two functions

Any solutions?

10. ## Re: Combining two functions

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

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?

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"

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

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)

15. ## Re: Combining two functions

Here you go this is the updated file

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)

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.

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.

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

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))

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?

22. ## Re: Combining two functions

see upper formula

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?

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.

25. ## Re: Combining two functions

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

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

#### 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