JARichard74
Board Regular
- Joined
- Dec 16, 2019
- Messages
- 114
- Office Version
- 365
- Platform
- Windows
As an example I have three numbers: 3.95, 4.95, 2.00 and the following code is used to produce the following formula
{=MIN(IF((B20:B22 < 5) * (B20:B22 > 2),B20:B22))} How do I get the formula to show {=MIN(IF((B20:B22 < 5.13) * (B20:B22 > 2.13),B20:B22))} i.e. number format 0.00. In the example Hrngel = 1.50, Hrngeh = 3.63
{=MIN(IF((B20:B22 < 5) * (B20:B22 > 2),B20:B22))} How do I get the formula to show {=MIN(IF((B20:B22 < 5.13) * (B20:B22 > 2.13),B20:B22))} i.e. number format 0.00. In the example Hrngel = 1.50, Hrngeh = 3.63
VBA Code:
Hrngel = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) - WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow))
Hrngeh = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) + WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow))
ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 - z).FormulaArray = "=MIN(IF((B" & startRow & ":B" & endRow & " < " & Hrngeh & ") * (B" & startRow & ":B" & endRow & " > " & Hrngel & "),B" & startRow & ":B" & endRow & "))"