large minus counta

[bluepenink]

Hello

having some trouble with the large function.

basically i have a selection from a drop down list which shows a list of cities in a state. some state have 8 cities, while some have 5 etc.

my large formula is:
=(LARGE($D$64:$D$73,F64)
where range D = city
where range f = number 1-8

now, when i change a state that has 5 cities, i get a #N/A error so i tried to incorporate a counta to offset any blank space, but still getting N/A error

can someone help with my logistic here, thxs so much

=IF(D64="","",(LARGE($D$64:$D$73,F64)-COUNTA($D$64:$D$73)))

basically, im saying, if D64 is blank then do nothing or N/A, otherwise use tthe large function.....

what am i doing wrong?

cheers

 

[Gerald Higgins]

Does D64:D73 contain the NAMES of the cities ?
If yes, the LARGE function won't work properly on them.

 

[Aladin Akyurek]

LARGE requires a numeric refernce. You say that the D range houses cities. If so, this invalidates the LARGE formula. Don't you get a #VALUE! error.

 

[scottylad2]

Does D64:D73 contain the NAMES of the cities ?
If yes, the LARGE function won't work properly on them.
__________________
LARGE requires a numeric refernce. You say that the D range houses cities. If so, this invalidates the LARGE formula. Don't you get a #VALUE! error.

 

[T. Valko]

Hello

having some trouble with the large function.

basically i have a selection from a drop down list which shows a list of cities in a state. some state have 8 cities, while some have 5 etc.

my large formula is:
=(LARGE($D$64:$D$73,F64)
where range D = city
where range f = number 1-8

now, when i change a state that has 5 cities, i get a #N/A error so i tried to incorporate a counta to offset any blank space, but still getting N/A error

can someone help with my logistic here, thxs so much

=IF(D64="","",(LARGE($D$64:$D$73,F64)-COUNTA($D$64:$D$73)))

basically, im saying, if D64 is blank then do nothing or N/A, otherwise use tthe large function.....

what am i doing wrong?

cheers

It's not real clear what you're trying to do.

What is the purpose of this formula? Is it supposed to determine how many items are displayed in your drop down list?

 

[scottylad2]

Are you trying to count the largest number of cities mentioned? ie say the city thats mentioned the most, then second most and third most etc etc

 

[bluepenink]

hello guys

sorry i was unable to respond right away (meeting)

the purpose of the large function is to show obv. values from top to bottom of the cities generating revenue.

im graphing those values so it was giving me problems when i selected a state that had less cities than a full 8 city state.

and yes, range D is the range where the city name is stored.

much appreciated.

 

[bluepenink]

Are you trying to count the largest number of cities mentioned? ie say the city thats mentioned the most, then second most and third most etc etc

yes, that is what my goal is, but when the city count is i.e. 5, then the formula shows the error. :S

 

[Aladin Akyurek]

hello guys

sorry i was unable to respond right away (meeting)

the purpose of the large function is to show obv. values from top to bottom of the cities generating revenue.

im graphing those values so it was giving me problems when i selected a state that had less cities than a full 8 city state.

and yes, range D is the range where the city name is stored.

much appreciated.

A1: Tokyo
A2: London
A3: Paris

=LARGE(A1:A3,2) ===> #VALUE!

Since you have city names in the D range, the bit with

LARGE($D$64:$D$73,F64)

will equally fail.

 

[mikerickson]

If =LARGE($D$64:$D$73,F64) works when there are more than (or=) F64 cities, how about

=IF(COUNT($D$64:$D$73)>F64,"not enough data", LARGE($D$64:$D$73,F64))