• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF nested formula

S

saransh

New Member
Hi all,

first of all, thanx chandoo. your website for excel was like finding a chest of gold.


now coming to my query.


in our company, any drawing passes through sets of reviews.

lets call them Z1, Z2, Z3, Z4 & Z5.


If for any drawing, Z1 review is complete, we call it 20% complete, completion of Z2 is 40% complete........and 100% for Z5.


we check status of drawing on a particular date. we call it data date.


so i want to put a nested IF, where in ask excel to check that, how much % of drawing review is completed.


If i am putting formula in column F3, with % mentioned in column A1 as 20% & date in A2 for Z1,with % mentioned in column B1 as 40% & date in B2 for Z2.......and so on.


i want excel to check backward staring from Z5, if the date of Z5 is lesser than present present date, it should say 100% review is completed, if not, then it should check previous couln and so on..


i hope i am clear in posting my query.


rgrds,
 
You could certainly do a 5 layer nested IF, but we don't have to. We really want to lookup up the date less than or equal to today and return corresponding percentage. We'll also need to take into account that sometimes we might be at 0%. Formula then is:

=IFERROR(INDEX(A1:E1,MATCH(F5,A2:E2)),0)
 
I dont know where it is now, but Chandoo (or someone) posted a to-do list that had a nice visual indicating % complete based on the number of items checked...


FOUND IT!: http://chandoo.org/wp/2013/01/07/todo-list-with-priorities/


this is just the first thing that came to mind... it may not be exactly what your looking for but .....
 
thanx all for the replies.


@Jason, the link was quite useful but not fulfilling my intention.


@Luke, if you can explain me this formula, may be i can modify this with my requirement, as in actual file i am having few other columns as well between A1, A2...

also, what is F5??


:) thnx in anticipation.
 
Hi Saransh ,


The F5 is probably because Luke has assumed that F5 will contain the date to be used for matching ; if it will always be today's date , you can use :


=IFERROR(INDEX(A1:E1,MATCH(TODAY(),A2:E2)),0)


What this formula is doing is checking today's date with the dates in the range A2:E2 ; MATCH with a last parameter which is omitted , is the same as MATCH with a last parameter 1 ; what this does is :



it finds the largest value that is less than or equal to lookup value




In your case , the lookup value is today's date. So , the MATCH function returns that largest date in the range A2:E2 , which is less than or equal to today's date.


Using the INDEX function allows us to return the corresponding value from the range of percentages , which is A1:E1.


If your range of percentages is in a range , say J5:N5 , put that reference in place of A1:E1 ; similarly , if your dates are in the range J6:N6 , put that reference in place of A2:E2. The important requirement is that the number of columns in both references should be identical.


Narayan
 
:-(.....its still not working.

my boss is hovering over me for this.


please suggest if i can send you the file to have a look.


F1 F1 F1 F1...
 
Hi Saransh ,


If it is not working , it can only mean two things :


1. Your version of Excel does not support the IFERROR function


2. You have not specified your problem either fully or correctly.


Luke's formula will give the correct result to the following problem specification :


The range A1:E1 has percentages , 20 % , 40% , 60% , 80% and 100%.


The range A2:E2 has dates.


The formula which can be entered in any unused cell will give the percentage corresponding to the greatest date in A2:E2 which is less than today's date.


If that is not your problem , please clarify.


Narayan
 
hi narayan,

its difficult to explain my issue untill i show how my file looks like.


is there any way by which i can post sample file here???


rgrds,

saransh
 
Hi Saransh ,


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
You must log in or register to reply here.
Back
Top