• 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(AND) vs a Nested IF?

3G

3G

Member
Hello-

I have a list of project numbers, resource names, hours and months.


What I'd like to do is assign a resource number to each resource (i.e. 1, 2, 3) per project. The column for Resource names has mulitiple entries per name, based on the number of months of the project. For example:


Project Resource Name Resource Number Month

3 Doug 1 01/2012

3 Doug 1 02/2012

3 Steve 2 01/2012

3 Steve 2 02/2012

4 James 1 01/2012


My problem is the resource number column...It keeps increasing the number, and, not starting over at 1 like I'd like it to above (note, James is "1" of new project, on my sheet he shows up as "3"). Here's my formula:


=IF(AND(A4=A3,B4=B3),C3,C3+1)


So, I need 2 criteria, and, not sure if I should do a nested IF, or, if the AND is throwing the wrench.


Thoughts?


3G
 
Hi, 3G!

Try with this formula from C2 and down:

=SI(A2=A1;SI(B2=B1;VALOR(C1);MAX(C$1:C1)+1);1) -----> in english: =IF(A2=A1,IF(B2=B1,VALUE(C1),MAX(C$1:C1)+1),1)

Regards!
 
Hi SirJB7!

THanks for the response. The formula unfortunately stops working after the first change in project number:


PNum Name Rnum Mo

2 Joe 1 2012/01

2 Bob 2 2012/01

2 Bob 2 2012/02

2 Bob 2 2012/02

2 Jerry 3 2012/02

3 Doug 1 2012/01

3 Doug 1 2012/02

3 Fred 4 2012/01

3 Fred 4 2012/02

3 Fred 4 2012/03


Note Fred is now 4, not 2.


Thoughts?


Thanks!
 
Hi, 3G!

This wasn't so clear in first post and the formula worked for the sample data.

But wait a moment! It still works for this second data set.

The data you posted as Rnum is the real number or the formula calculation? If first, the formula gives equal values, so it works! If second, which are the real ones?

Do you want an uploaded sheet to check this?

Regards!
 
Hi ,


I am not sure I have understood what you want ; assuming that in the second set of data that you have posted , Fred should have a resource number of 2 , instead of 4 , I think the following should do the job :


=IF(A3=A2,IF(COUNTIF($B$2:B3,B3)>1,C2,C2+1),1)


Put the above formula in C3 and copy downwards ; in C2 , you can have the same formula , but I would assume that the first entry would always be 1 , so you can just enter the number 1.


This assumes that you have headers in row 1 , and your data starts from row 2.


Narayan
 
@NARAYANK991

Hi!

Specs problem, I guess.

Could you do me a favour? Please check if my formula works for both data sets, I think it does but 3G comments lead me to doubt.

Regards!
 
Hi SirJB7 ,


I checked the formula , and it is giving results different from what 3G expects ( I think ).


Basically what 3G wants is that whenever the project changes , the resource number should start from 1 ; within the same project , the resource number should change whenever the Resource Name changes.


For the formula that I have given to work , the data should be sorted on Project first , and Resource Name next.


Narayan
 
@NARAYANK991

Hi!

Maybe it's my mistake for what I understood from Sara.Gan's phrase "Note Fred is now 4, not 2.": I assumed 4 was the correct value and not 2, which was supposed to be given by my formula... which indeed give a value of 4 accordingly to first data set (so I didn't change it!). That's what I asked Sara.Gan to confirm. Let's wait the comments.

Thanks for checking it out for me.

Regards!
 
Hi Narayank991 & SirJB7


NarayanK's formula worked. Correct SirJB7, the resource withIN the project needed to start over at 1. No problem. I will try to do a better job of explaining the issue next time.


Regardless, I sincerely appreciate the help sir!


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