Excel Formula Question!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This formula works fine but it enters results before all data has been entered.

=INDEX(B10:B49,IF(COUNT(AP10:AP49)=COUNTIF(AP10:AP49,"<0"),MATCH(MAX(AP10:AP49),AP10:AP49,0),MATCH(MIN(IF(AP10:AP49>=0,AP10:AP49)),AP10:AP49,0)))

My question is can this be prevented until the all data has been entered?

Thanks ahead!
 
Which range are you "keying" on that needs to have all the data entered?

You should be able to add an IF function to the beginning of the formula to
test that "all data" has been entered. Sort of like this:

=IF(COUNT(B10:B49)<40,"",INDEX(................))))

That will leave the cell blank until *every* cell in B10:B49 has a number in
it.
 
Well B10:B49 has a list of names in alphabetical order only

AN10:AN49 has no numbers entered yet and until then it shouldn’t list a
person name with this formula below is what I’m trying to-do.

=INDEX(B10:B49,IF(COUNT(AP10:AP49)=COUNTIF(AP10:AP49,"<0"),MATCH(MAX(AP10:AP49),AP10:AP49,0),MATCH(MIN(IF(AP10:AP49>=0,AP10:AP49)),AP10:AP49,0)))
 
Ok, then use something like this:

=IF(COUNT(AN10:AN49)<40,"",INDEX(................))))
 
Back
Top