# Posts by dejamls

• ## Average and Median formula with conditions

A better example attached.

Corrected some text regarding method I used.

Methodology

\$I\$1 = lower value of 0.3

\$K\$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 1 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

Code in the sample

Code
1. =IF(AND(AND(\$H3>=\$I\$1,\$H3<=\$K\$1),AND(\$I3>=\$I\$1,\$I3<=\$K\$1),AND(\$J3>=\$I\$1,\$J3<=\$K\$1)),MEDIAN(\$B3:\$D3),IF(AND(AND(\$H3>=\$I\$1,\$H3<=\$K\$1),AND(\$I3>=\$I\$1,\$I3<=\$K\$1),OR(\$J3<\$I\$1,\$J3>\$K\$1)),(\$B3+\$C3)/((\$B3<>0)+(\$C3<>0)),IF(AND(AND(\$H3>=\$I\$1,\$H3<=\$K\$1),OR(\$I3<\$I\$1,\$I3>\$K\$1),AND(\$J3>=\$I\$1,\$J3<=\$K\$1)),(\$B3+\$D3)/((\$B3<>0)+(\$D3<>0)),IF(AND(OR(\$H3<\$I\$1,\$H3>\$K\$1),AND(\$I3>=\$I\$1,\$I3<=\$K\$1),AND(\$J3>=\$I\$1,\$J3<=\$K\$1)),(\$C3+\$D3)/((\$C3<>0)+(\$D3<>0)),IF(AND(OR(\$H3<\$I\$1,\$H3>\$K\$1),OR(\$I3<\$I\$1,\$I3>\$K\$1),AND(\$J3>=\$I\$1,\$J3<=\$K\$1)),\$D3,IF(AND(OR(\$H3<\$I\$1,\$H3>\$K\$1),AND(\$I3>=\$I\$1,\$I3<=\$K\$1),OR(\$J3<\$I\$1,\$J3>\$K\$1)),\$C3,IF(AND(AND(\$H3>=\$I\$1,\$H3<=\$K\$1),OR(\$I3<\$I\$1,\$I3>\$K\$1),OR(\$J3<\$I\$1,\$J3>\$K\$1)),\$B3,IF(AND(OR(\$H3<\$I\$1,\$H3>\$K\$1),OR(\$I3<\$I\$1,\$I3>\$K\$1),OR(\$J3<\$I\$1,\$J3>\$K\$1)),"B Jars outside value guideline","Problem"))))))))
• ## Average and Median formula with conditions

Apologies. Sample attached.

## Files

• Example.xlsx

• ## Average and Median formula with conditions

I believe I may have it sorted. Maybe there is a better way.

 AF AG AH AL AM AN AO Row 2 Aug/LJAR 1 Aug/LJAR 2 Aug/LJAR 3 Bg/LJAR 1 Bg/LJAR 2 Bg/LJAR 3 AFinalug/L Row 3 43 25 100 1.36 0.01 12.00 ?

Code
1. =IF(AND(AND(\$AL3>=\$AM\$1,\$AL3<=\$AO\$1),AND(\$AM3>=\$AM\$1,\$AM3<=\$AO\$1),AND(\$AN3>=\$AM\$1,\$AN3<=\$AO\$1)),MEDIAN(\$AF3:\$AH3),IF(AND(AND(\$AL3>=\$AM\$1,\$AL3<=\$AO\$1),AND(\$AM3>=\$AM\$1,\$AM3<=\$AO\$1),OR(\$AN3<\$AM\$1,\$AN3>\$AO\$1)),(\$AF3+\$AG3)/((\$AF3<>0)+(\$AG3<>0)),IF(AND(AND(\$AL3>=\$AM\$1,\$AL3<=\$AO\$1),OR(\$AM3<\$AM\$1,\$AM3>\$AO\$1),AND(\$AN3>=\$AM\$1,\$AN3<=\$AO\$1)),(\$AF3+\$AH3)/((\$AF3<>0)+(\$AH3<>0)),IF(AND(OR(\$AL3<\$AM\$1,\$AL3>\$AO\$1),AND(\$AM3>=\$AM\$1,\$AM3<=\$AO\$1),AND(\$AN3>=\$AM\$1,\$AN3<=\$AO\$1)),(\$AG3+\$AH3)/((\$AG3<>0)+(\$AH3<>0)),IF(AND(OR(\$AL3<\$AM\$1,\$AL3>\$AO\$1),OR(\$AM3<\$AM\$1,\$AM3>\$AO\$1),AND(\$AN3>=\$AM\$1,\$AN3<=\$AO\$1)),\$AH3,IF(AND(OR(\$AL3<\$AM\$1,\$AL3>\$AO\$1),AND(\$AM3>=\$AM\$1,\$AM3<=\$AO\$1),OR(\$AN3<\$AM\$1,\$AN3>\$AO\$1)),\$AG3,IF(AND(AND(\$AL3>=\$AM\$1,\$AL3<=\$AO\$1),OR(\$AM3<\$AM\$1,\$AM3>\$AO\$1),OR(\$AN3<\$AM\$1,\$AN3>\$AO\$1)),\$AF3,IF(AND(OR(\$AL3<\$AM\$1,\$AL3>\$AO\$1),OR(\$AM3<\$AM\$1,\$AM3>\$AO\$1),OR(\$AN3<\$AM\$1,\$AN3>\$AO\$1)),"B Jars outside value guideline","Problem"))))))))

Methodology

\$AM\$1 = lower value of 0.3

\$AO\$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

• ## Average and Median formula with conditions

Hi

Please can someone help me with a formula.

I have these columns

 A ug/L JAR 1 A ug/L JAR 2 A ug/L JAR 3 B g/L JAR 1 B g/L JAR 2 B g/L JAR 3 AFinal ug/L 43 25 100 1.36 0.01 12.00 ?

I need a formula if possible to calculate a "A Final ug/L" result based on certain conditions.

Conditions is:

1. If a particular B group JAR is below 0.3 g/L or above 3.0 g/L then the corresponding A group JAR sample should be rejected in calculations for final value.

2. If one of the three corresponding A group JAR sample is rejected then the remaining two A group JAR samples are calculated as mean value (average) in the final value.

3. If two of the corresponding A group JAR sample is rejected then the remaining one A group JAR samples is just the final value.

4. If no JARs rejected then all three A group JAR samples calculated as median (middle value) value.

5. Zero values and blanks not included in calculation.

I really appreciate any help given.

Thanks

• ## Click a IE9 Web Page button using VBA

Re: Click a IE9 Web Page button using VBA

Send keys not working.

Cheers for the assistance John.

Going to play with the code bit more.

• ## Click a IE9 Web Page button using VBA

Re: Click a IE9 Web Page button using VBA

I will try send keys see if works ok. Tab to position and enter.

• ## Click a IE9 Web Page button using VBA

Re: Click a IE9 Web Page button using VBA

First of all I took any error handling code out of my macro so it didn't exit sub.

I tried stepping through code and got Runtime error'438': Object doesn't support the property or method.

With the Ctrl+G action I got Runtime error '91': Object variable or with block variable not set.

I tried new code too and no luck.

After code line

Code
1. [COLOR=#333333]evtClick.initEvent "click", [/COLOR][COLOR=blue]True[/COLOR][COLOR=#333333], [/COLOR][COLOR=blue]False[/COLOR]

I got the Runtime error '438'

• ## Click a IE9 Web Page button using VBA

Re: Click a IE9 Web Page button using VBA

That got rid of the error, cheers but...

The button still won't click. There is two instances of the class "t13Button". I've tried try (1), (2), etc.

No luck.

I also tried the class "t13RegionButtons"

Thoughts John.

Cheers

• ## Click a IE9 Web Page button using VBA

Re: Click a IE9 Web Page button using VBA

I'm getting a compile error with the reset button code added: (This line - Dim resetButton As HTMLInputElement) "user-defined type not defined".

Without the reset button code everything works fine.

My excel knowledge is limited. Appreciate your help.

Snippet of the code:

• ## Click a IE9 Web Page button using VBA

Hi good people.

I am trying to find some code to click this button in the below html code.

Code
1. <tr><th class="t13RegionTitle">Find Isolation Lists</th>
2. <th class="t13RegionButtons" valign="bottom"><input type="button" onclick="javascript:apex.submit(&#x27;RESET&#x27;);" value="Reset Quick Search" class="t13Button" />&nbsp;&nbsp;&nbsp;</th>
3. </tr>

I have used code successfully for opening a Intranet URL and clicking on a specific hypertext link on IE9 Web Page, but cannot work out how to write the correct code to click this particular button.

Any help appreciated.

Cheers

• ## Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to 1

Re: Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to

I have it working .

Big smiles.

Many thanks.

• ## Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to 1

Re: Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to

Hi

I used the formula including the array function.

=INDEX(Sheet2!\$E\$2:\$E\$4,MATCH(A2&C2,Sheet2!\$A\$2:\$A\$4&Sheet2!\$C\$2:\$C\$4,0),1)

But it returned in Sheet1 E2 #N/A etc.

• ## Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to 1

Re: Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to

Quote from MrRedli;770299

can attach a excel file with what output you need

Hi

Sorry cant upload because I am at work using work PC.

But here is the scenario.

Sheet1 I want to fill column E with data from Sheet2.
[TABLE="width: 873"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[/tr]

[tr]

[td]

2996

[/td]

[td]

Tree

[/td]

[td]

7710

[/td]

[td]

OUTDOOR CONSUMABLES

[/td]

[td][/td]

[/tr]

[tr]

[td]

2997

[/td]

[td]

Grass

[/td]

[td]

7710

[/td]

[td]

OUTDOOR CONSUMABLES

[/td]

[td][/td]

[/tr]

[tr]

[td]

2998

[/td]

[td]

Dirt

[/td]

[td]

7720

[/td]

[td]

GENERAL CONSUMABLES

[/td]

[td][/td]

[/tr]

[tr]

[td]

2999

[/td]

[td]

Stones

[/td]

[td]

7730

[/td]

[td]

TOOL CONSUMABLES

[/td]

[td]

[/td]

[/tr]

[/TABLE]

Sheet2 Note that Sheet2 data is not ordered the same as Sheet1.
[TABLE="width: 873"]

[tr]

[td]

[TABLE="width: 873"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[/tr]

[tr]

[td]

2996

[/td]

[td]

Tree

[/td]

[td]

7710

[/td]

[td]

OUTDOOR CONSUMABLES

[/td]

[td]

100

[/td]

[/tr]

[tr]

[td]

2998

[/td]

[td]

Dirt

[/td]

[td]

7720

[/td]

[td]

GENERAL CONSUMABLES

[/td]

[td]

250

[/td]

[/tr]

[tr]

[td]

2999

[/td]

[td]

Stones

[/td]

[td]

7730

[/td]

[td]

TOOL CONSUMABLES

[/td]

[td]

100

[/td]

[/tr]

[tr]

[td]

2997

[/td]

[td]

Grass

[/td]

[td]

7710

[/td]

[td]

OUTDOOR CONSUMABLES

[/td]

[td]

150

[/td]

[/tr]

[/TABLE]

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

Using Sheet1 columns A and C match Sheet2 columns A and C and return Sheet2 column E value to Sheet1 column E.

Sheet1 end result
[TABLE="width: 873"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[/tr]

[tr]

[td]

2996

[/td]

[td]

Tree

[/td]

[td]

7710

[/td]

[td]

OUTDOOR CONSUMABLES

[/td]

[td]

100

[/td]

[/tr]

[tr]

[td]

2997

[/td]

[td]

Grass

[/td]

[td]

7710

[/td]

[td]

OUTDOOR CONSUMABLES

[/td]

[td]

150

[/td]

[/tr]

[tr]

[td]

2998

[/td]

[td]

Dirt

[/td]

[td]

7720

[/td]

[td]

GENERAL CONSUMABLES

[/td]

[td]

250

[/td]

[/tr]

[tr]

[td]

2999

[/td]

[td]

Stones

[/td]

[td]

7730

[/td]

[td]

TOOL CONSUMABLES

[/td]

[td]

100

[/td]

[/tr]

[/TABLE]

• ## Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to 1

Hi I have tables on Sheet1 and Sheet2. The table on Sheet2 has many more rows of data than Sheet1.

I need to search Sheet2 columns A and C for matching values in Sheet1 columns A and C and when found copy corresponding valve in Sheet2 column E to Sheet1 column E.

Is there a formula for this or does it need vba?

Table data looks like this in both Sheets except Sheet2 will have values in the Sum of Actual column and many more rows of data;

[TABLE="width: 656"]

[tr]

[TD="class: xl81, width: 100, align: center"]A[/TD]
[TD="class: xl81, width: 280, align: center"]B[/TD]
[TD="class: xl81, width: 114, align: center"]C[/TD]
[TD="class: xl81, width: 229, align: center"]D[/TD]
[TD="class: xl82, width: 150, align: center"]E[/TD]

[/tr]

[tr]

[TD="class: xl77, width: 100"]2996[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Tree[/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7710[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]OUTDOOR CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[/tr]

[tr]

[TD="class: xl77, width: 100"]2997[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Grass[/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7710[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]OUTDOOR CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[/tr]

[tr]

[TD="class: xl77, width: 100"]2998[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Dirt [/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7720[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]GENERAL CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[/tr]

[tr]

[TD="class: xl77, width: 100"]2999[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Stones[/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7730[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]TOOL CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[/tr]

[/TABLE]

• ## Search Folders And Create Hyperlinks To Files in Excel 2007

Re: Search Folders And Create Hyperlinks To Files in Excel 2007

Hi

Could someone help to modify the 2nd code so that it;

1/ Searches sub-folders as well and
2/ pastes the hyperlink of the matching document in row B.
3/ if a matching document is not found then skips that row and continues on.

• ## VBA code to calculate number of dates in column that are after todays date

Re: VBA code to calculate number of dates in column that are after todays date

Solution I finally got was inputing formula into cell that I wanted result in and then - clicked record macro - clicked cell - clicked F2 - hit enter - stopped recording - copied recorded VBA into code.

Works!

If there is a better way then glad to hear.

• ## VBA code to calculate number of dates in column that are after todays date

Re: VBA code to calculate number of dates in column that are after todays date

Hi

Still not working.

The two excel formulas I need converted to VBA if anyone can help, much appreciatted.

They work but I need them in VBA.

=COUNTIF(SOPs!L:L, "<=" & TODAY())

=COUNTIFS(SOPs!L:L,"<="&TODAY()+90,SOPs!L:L,">="&TODAY())

• ## VBA code to calculate number of dates in column that are after todays date

Re: VBA code to calculate number of dates in column that are after todays date

Hello experts

Please can you help with my coding.

I have tested it but it doesn't count properly.

The first part works but the counting for 'prior to TODAYS date' and 'next 90 days from TODAYS date' doesn't.

Regards
Dejamls

• ## VBA code to calculate number of dates in column that are after todays date

Re: VBA code to calculate number of dates in column that are after todays date

OK

I think I got the countif VBA to work.

Code
1. Dim x As Long
2. Dim z As Date
3. z = Date
4. With Sheets("SOPs")
5. x = Application.WorksheetFunction.CountIf(.Columns("L:L"), ">= & z")
6. End With
7. Worksheets("MTD Stats").Range("C19").Value = x

Appears to work fine.

• ## VBA code to calculate number of dates in column that are after todays date

Hi

I need a VBA formula that looks at a column of a large number of dates and calculates the number of dates that are >= todays date.

I have the excel formula

Code
1. =countif(L:L,">="&today())

But how is that expressed in VBA?

I also need seperate VBA code to count the dates between TODAY and +90 days time.

All help appreciated.

Thanks