Fill in missing data based on another cell

[Linh Le]

Hello,

Hoping the community can help me with the following:

I have a list of company that have different product lines that i would like to assign a rating dummy variable "1" if it meets certain criterion, e.g., "High", "Above Ave.", etc...For some reasons certain company product line gets rated while others don't, i.e., Company E has 27 products but 8 of them don't have a rating whereas the other 19 has a rating of "Ave.".

This is part of the formula i'm using: IF(Y65="High",1,IF(Y65="High^Q",1,IF(Y65="Above Average",1,IF(Y65="Above Average^Q",1,IF(Y65="High",1,IF(Y65="High^Q",1,IF(Y65="Above Average",1,IF(Y65="Above Average^Q",1,0))))))))

Question: Is there a formula to fill in the missing info based on the previous rating w/o either filter or manually type it in as i have 5000 rows to comb thru? Hoping the screenshot can provide more info.

Thank you,
Le

 

[dreid1011]

Hello,

Hoping the community can help me with the following:

I have a list of company that have different product lines that i would like to assign a rating dummy variable "1" if it meets certain criterion, e.g., "High", "Above Ave.", etc...For some reasons certain company product line gets rated while others don't, i.e., Company E has 27 products but 8 of them don't have a rating whereas the other 19 has a rating of "Ave.".

This is part of the formula i'm using: IF(Y65="High",1,IF(Y65="High^Q",1,IF(Y65="Above Average",1,IF(Y65="Above Average^Q",1,IF(Y65="High",1,IF(Y65="High^Q",1,IF(Y65="Above Average",1,IF(Y65="Above Average^Q",1,0))))))))

Question: Is there a formula to fill in the missing info based on the previous rating w/o either filter or manually type it in as i have 5000 rows to comb thru? Hoping the screenshot can provide more info.

Thank you,
Le

Your formula looks like it repeats itself about halfway through. Is that intentional? Are you able to create a lookup table somewhere to hold the Criteria and Ratings?

 

[Linh Le]

Hi, yes. that's intentional because I also want to capture ratings marked "Q" that are assigned by a bot. I have not use lookup table before...

 

[dreid1011]

Hi, yes. that's intentional because I also want to capture ratings marked "Q" that are assigned by a bot. I have not use lookup table before...

IF(Y65="High",1,IF(Y65="High^Q",1,IF(Y65="Above Average",1,IF(Y65="Above Average^Q",1,IF(Y65="High",1,IF(Y65="High^Q",1,IF(Y65="Above Average",1,IF(Y65="Above Average^Q",1,0))))))))

Doesn't make sense to me as you're checking for the same values twice. Anyway, with a lookup table, you can use XLOOKUP to pull the rating value based on the criteria value like so:

Book1
	A	B	C	D	E
1	Criteria	Rating		Results
2	High	1		High	1
3	High^Q	1		High^Q	1
4	Above Average	1		Average	-
5	Above Average^Q	1		Above Average	1
6				Above Average^Q	1
7				^Q	-
Lookup Values

Cell Formulas
Range	Formula
E2:E7	E2	=XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,"-")