Excel help needed

[ITDWARRIOR]

Salam
Dear friends maine 2 files banai hain.. file 1 main maine formula lagaya hai vlookup ka jo k data match ker raha hai file 2 se.

magar problem yeh hai ke file 1 main maine jahan formula lagaya hai wahan
"#value" likha hua ata hai jab file 2 open nahin hoti. or jab file 2 open kerta hon tu fir match data show kerta hai.. main chahta hon k jo data match ho rahi hai woh waisi he rahy chahy file 2 close bhi ker don..

agar kisi ko koi tareqa ya famula pata hai tu please bataiye..
Thanks

[ussmaan]

may this forumulas helping u

Sometimes it is useful to convert an MxN array into a single column of data, for example for charting (a data series must be a single row or column). Click here for more details.

Averaging Values In A Range

You can use Excel's built in =AVERAGE function to average a range of values. By using it
with other functions, you can extend its functionality.

For the formulas given below, assume that our data is in the range A1:A60.

Averaging Values Between Two Numbers

Use the array formula

=AVERAGE(IF((A1:A60>=Low)*(A1:A60<=High),A1:A60))

Where Low and High are the values between which you want to average.

Averaging The Highest N Numbers In A Range

To average the N largest numbers in a range, use the array formula

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

Change "1:10" to "1:N" where N is the number of values to average.

Averaging The Lowest N Numbers In A Range

To average the N smallest numbers in a range, use the array formula

=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10"))))

Change "1:10" to "1:N" where N is the number of values to average.

In all of the formulas above, you can use =SUM instead of =AVERAGE to sum, rather
than average, the numbers.

Counting Values Between Two Numbers

If you need to count the values in a range that are between two numbers, for example between
5 and 10, use the following array formula:

=SUM((A1:A10>=5)*(A1:A10<=10))

To sum the same numbers, use the following array formula:

=SUM((A1:A10>=5)*(A1:A10<=10)*A1:A10)

Counting Characters In A String

The following formula will count the number of "B"s, both upper and lower case, in the string in B1.

=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(B1,"B",""),"b",""))