Flagging increases in values when a certain criteria is met

[MrSak87]

Hi all,

I use this site often but I'm usually able to find a solution to my problem through your threads, but not this time so it's my first post.

Essentially I have huge amounts of lab data and have been automatically generating a testing schedule from it using a small macro but most calculations come from the formula bar. My problem is for a certain test a criteria must be met for it to be performed. i.e

Column A Column B Column C Column D
[TABLE="width: 500"]
<tbody>[TR]
[TD]P1[/TD]
[TD]0[/TD]
[TD]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1B1[/TD]
[TD]0[/TD]
[TD]0.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1Q1[/TD]
[TD]0.2[/TD]
[TD]0.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1U1[/TD]
[TD]0.4[/TD]
[TD]0.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1B2[/TD]
[TD]0.6[/TD]
[TD]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]1[/TD]
[TD]1.64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2B1[/TD]
[TD]1[/TD]
[TD]1.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2B2[/TD]
[TD]1.2[/TD]
[TD]1.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2Q1[/TD]
[TD]1.4[/TD]
[TD]1.64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]2[/TD]
[TD]2.64[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



For every 1.5 increase (column B and C is just a start and end) I want excel in column D to write an X next to it so in the example above the "X" would appear at row "P2Q1". Normally this would be quite easy but the P1, P2, P3 show the start and end of the whole sample so whatever solution is found needs to only include subsamples P1B1,P1Q1 etc and ignore full samples P1, P2, P3.

My explanation is awful I know but hopefully you can see what I'm tying to do!! Any help would be much appreciated!

 

[MrSak87]

[TABLE="width: 313"]
<tbody>[TR]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0.78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1B1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]0[/TD]
[TD]0.2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1Q1[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]0.2[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1U1[/TD]
[TD][/TD]
[TD]U[/TD]
[TD]0.4[/TD]
[TD]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P1B2[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]0.6[/TD]
[TD]0.78[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2B1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]1.2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2B2[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1.2[/TD]
[TD]1.4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2Q1[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]1.4[/TD]
[TD]1.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3B1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]2.2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P3Q1[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]2.2[/TD]
[TD]2.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P3U1[/TD]
[TD][/TD]
[TD]U[/TD]
[TD]2.4[/TD]
[TD]2.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4B1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]3.2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P4Q1[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]3.2[/TD]
[TD]3.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P4Q2[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]3.4[/TD]
[TD]3.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P5B1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]4[/TD]
[TD]4.2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P5U1[/TD]
[TD][/TD]
[TD]U[/TD]
[TD]4.2[/TD]
[TD]4.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P5Q1[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]4.4[/TD]
[TD]4.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P5B2[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]4.6[/TD]
[TD]4.75[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P6[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5.62[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P6B1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]5[/TD]
[TD]5.15[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P6Q1[/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]5.15[/TD]
[TD]5.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P6B2[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]5.35[/TD]
[TD]5.62[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]P7[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]6.92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MrSak87]

So with the above only the x's are important. They should automatically appear at 1.4-1.64, 3-3.2, 4.4-4.6 and whatever the first P7 value will be...

 

[gaz_chops]

Apologies, just saw your post earlier - "basically every 1.5 increase from 0 needs to be flagged somehow. so 0, 1.5, 3, 4.5 etc"

 

[MrSak87]

aye that's the ticket

 

[gaz_chops]

OK, try the following

In Cell D2 enter
=IF(LEN(A2)=2,D1,(ROUNDDOWN(C2/1.5,0)*1.5))

In Cell E2 enter
=IF(D2=D1,"","X")

Copy both down full range.

 

[MrSak87]

Gaz you are a genius! I've had to make a few tweaks because I needed the real values of column D, so I've just added a hidden column and copied pretty much exactly what you've done.

Thankyou!!!!!!!!!!!!!

 

[gaz_chops]

You are welcome

 

[gaz_chops]

Just a thought, if your "whole samples" go beyond P9, you may have to tweak the formula "LEN(A2)=2" to accommodate!