high & low relational numbers

JAYLO · Apr 10th, 2002, 03:17 PM

Using the followiing SUMIF and COUNTIF functions I am able to get the # of occurences and the avg in relation to specific cells. How can I get largest and the smallest number in the same range?

dblVal = Application.SumIf(rngC, rng, rngD) ' - Application.SumIf(rngC, rng, rngB)
dblCnt = Application.CountIf(rngC, rng)
dblAvg = dblVal / dblCnt
MsgBox dblAvg

Al Chara · Apr 10th, 2002, 03:40 PM

max and min?

JAYLO · Apr 10th, 2002, 03:47 PM

could you me an example using my scenario?
TIA

Al Chara · Apr 10th, 2002, 03:58 PM

I don't know, depending on what range you want:

Application.WorksheetFunction.Max (rngD)

Application.WorksheetFunction.Min (rngD)

JAYLO · Apr 10th, 2002, 04:01 PM

Thats just it. The range is dependent on the results of the COUNTIF function. Thus my problem.

Al Chara · Apr 10th, 2002, 04:23 PM

I am getting lost in your variable named ranges. Can you give the actual cell ranges?

Apr 10th, 2002, 03:58 PM	#4
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	I don't know, depending on what range you want: Application.WorksheetFunction.Max (rngD) Application.WorksheetFunction.Min (rngD) __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 03:17 PM	#1
JAYLO Board Regular Join Date: Apr 2002 Posts: 210	Using the followiing SUMIF and COUNTIF functions I am able to get the # of occurences and the avg in relation to specific cells. How can I get largest and the smallest number in the same range? dblVal = Application.SumIf(rngC, rng, rngD) ' - Application.SumIf(rngC, rng, rngB) dblCnt = Application.CountIf(rngC, rng) dblAvg = dblVal / dblCnt MsgBox dblAvg

Apr 10th, 2002, 03:40 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	max and min?

Apr 10th, 2002, 03:47 PM	#3
JAYLO Board Regular Join Date: Apr 2002 Posts: 210	could you me an example using my scenario? TIA

Apr 10th, 2002, 04:01 PM	#5
JAYLO Board Regular Join Date: Apr 2002 Posts: 210	Thats just it. The range is dependent on the results of the COUNTIF function. Thus my problem.

Apr 10th, 2002, 04:23 PM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	I am getting lost in your variable named ranges. Can you give the actual cell ranges?