Combine nested if with lookup so calculates if certain conditions are

[Jediantman]

I have a bit of a nightmare with an IF function that will need to calculate based on the value of one cell. All very simple so far, except that there are 56 potential values for that cell so I can't use a nested if to just perform what I want. I've tried to combine it with a lookup to make it easier but I run into the same issue where I need to qualify the result with the same 56 combinations.


What I need to do is work out the date that a report is due, but that date must take into account the holidays of the authority the report is for. Out of the 56 authorities there are 8 different combinations of the holiday dates so I have used named ranges to make it easier to understand (and what I hoped - easier to calculate) but there obviously needs to be a way that can match which authority has been selected, then what holidays apply before returning the correct date.


This is what I've been working on and I've tried various alternatives with mixed result but the current format returns only the first part of the nested IF (basically if the value of F23 is found in my table it will refer to 'Rutlandhols'):


=IF(HLOOKUP(F23,Holidays!$K$2:$BM$19,1,FALSE),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,Rutlandhols)),IF((OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,NhantsHols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,SolHols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,DudleyLeicsHols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,Group1Hols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,Group2Hols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,HackIsliHols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,Group3Hols)),IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(BJ23,4,PboroHols)))))))))))


Help me Excel magicians, please....

 

[sgmpatnaik]

Hi Jediantman


can you upload a sample file what you will going to do


With regards


SP

 

[NARAYANK991]

Hi Ant ,


A few unrelated points :


1. Where ever possible , try to simplify formulae by using helper cells / columns , or in any other way. For example , in your formula , you are using the following construct several times :


OR(ISBLANK(L23),L23="")


What this does is check whether the cell L23 is either blank , meaning without any data , or has the 0 length string "" as a result of a formula.


To simplify this , you can do two things :


a) Replace the two function call ( you are using the OR function and the ISBLANK function ) with a single function call using LEN ; checking for the length = 0 will return TRUE whether the cell is blank ( which you are checking with ISBLANK ) or the cell has the 0 length string as a result of a formula. Thus , you can use =LEN(L23)=0.


b) Use an intermediate helper cell , say M23 , which contains the formula OR(ISBLANK(L23),L23="") ,

and in the main IF formula , use references to M23 instead of repeating OR(ISBLANK(L23),L23="").


2. This point is more relevant ; you are using a HLOOKUP to retrieve a value for F23 , and thereafter , you are checking for L23 to see whether it fulfills a certain condition ; can you reverse the order of these two checks ? If the value in L23 can be checked first , you don't have to repeat it so many times. It would now look like this :


=IF(OR(ISBLANK(L23),L23=""),"",IF(HLOOKUP(F23,Holidays!$K$2:$BM$19,1,FALSE),....))


Even here , I have some doubts ; you say you have 8 holiday combinations , but I find there are 9 references :


Rutlandhols

NhantsHols

SolHols

DudleyLeicsHols

Group1Hols

Group2Hols

HackIsliHols

Group3Hols

PboroHols


Also , I don't understand how the selection of which set of holidays to use is being done ; all I can see is that the start date is in BJ23 ; on what BJ23 depends is not known.


All in all , I think you are going about designing the formula the round about way ; you have already decided you want to use the IF statement , the HLOOKUP statement and so on. I think you should first put down in writing the logic you want to implement , in plain English. Satisfy yourself that you have formulated this absolutely correctly , and comprehensively , and then set about converting this plain English formulation into an Excel formula.


If you can just give us the plain English formulation , we can suggest the formula you should use.


Narayan