Excel Formula Question!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Killer - 22 Sep 2007 05:06 GMT

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!

Reply to this Message

T. Valko - 22 Sep 2007 05:40 GMT

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.

Signature

Biff
Microsoft Excel MVP

> This formula works fine but it enters results before all data has been
> entered.
[quoted text clipped - 4 lines]
>
> Thanks ahead!

Reply to this Message

Killer - 22 Sep 2007 21:54 GMT

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)))

> Which range are you "keying" on that needs to have all the data entered?
>
[quoted text clipped - 14 lines]
> >
> > Thanks ahead!

Reply to this Message

T. Valko - 23 Sep 2007 03:14 GMT

Ok, then use something like this:

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

Signature

Biff
Microsoft Excel MVP

> Well B10:B49 has a list of names in alphabetical order only
>
[quoted text clipped - 24 lines]
>> >
>> > Thanks ahead!

Reply to this Message