If and Max function together - VBA

[drsudhakar]

Dear all
I need to get average, max and min of cells (all numbers) in column P, if data in column R is = 1 or >1.

My dataset has varying number of rows depending on amount of work happened, for which I am using dynamic last row (LR). I could figure out the formula for average as below :

sheet.Range("P" & LR + 2).Formula = "=AVERAGEif(r2:r" & LR & ", "">1"", P2:P" & LR & ")"


But, for max or min function, the same is pending. Code I came for Max is as follows :

sheet.Range("P" & LR + 3).FormulaArray = "=MAX(if(r2:r" & LR & ", "">1"", P2:P" & LR & "), )"



Can somebody help please.

Thanks in advance for sharing your knowledge. I am a biologist and new to VBA.

regards
drsudhakar

 

[wideboydixon]

Try it like this:

sheet.Range("P" & LR + 3).FormulaArray = "=MAX(IF(R2:R" & LR & ">1", P2:P" & LR & "))"

WBD

 

[drsudhakar]

Try it like this:

sheet.Range("P" & LR + 3).FormulaArray = "=MAX(IF(R2:R" & LR & ">1", P2:P" & LR & "))"

WBD

Hi wideboydixon
Thanks for suggestion. But, the array provided shows compile error in Excel 2013. Could you please identify the issue.


regards
drsudhakar

 

[wideboydixon]

Oops

sheet.Range("P" & LR + 3).FormulaArray = "=MAX(IF(R2:R" & LR & ">1,P2:P" & LR & "))"

WBD

 

[drsudhakar]

Hi
Thanks a lot for the prompt response. The array work well now.

 

[drsudhakar]

Hi all
I thought, replacing Max with MIN in the above formula will provide minimum value. But, that doesn't work. Can somebody help please.


sheet.Range("Q" & LR + 4).Formula = "=MIN(IF(R2:R" & LR & ">1, P2:P" & LR & "))"

Advance thanks for response.

regards
drsudhakar

 

[wideboydixon]

You need to set .FormulaArray, not .Formula

WBD

 

[drsudhakar]

You need to set .FormulaArray, not .Formula

WBD

Hi WBD
Thanks again for the clarification. Its a formula Array.

regards
drsudhakar


Known is a drop, unknown is an Ocean - Avvaiyar