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!

 

[gaz_chops]

I don'y understand the 1.5 increase! How is 1.64 a 1.5 increase over 1.4?

 

[Andrew Poulsom]

Or even over the starting number for P" of 1? Maybe in D1 copied down:

=IF(LEN(A1)>2,IF(C1>=1.5,"X",""),"")

 

[gaz_chops]

I was thinking the increase is C over B?
=IF(AND((LEN(A1)>2),((C1-B1)>=1.5)),"X","")

Ah it is the subsample over the sample start point!

 

[MrSak87]

Yea the subsample over the sample start point so basically every 1.5 increase from 0 needs to be flagged somehow. so 0, 1.5, 3, 4.5 etc. I'll give what you said a blast now see what happens. Thankyou for responding

 

[gaz_chops]

=if(and((len(a1)>2),(c1>=1.5)),"x","")

 

[MrSak87]

What's happening is before the first 1.5 everything is being ignored as wanted but then I don't want another X for another 1.5. What happens is I have blank to 1.5 then everything after the first X has an X....... So close!

 

[gaz_chops]

Strange it looks fine on my data!
Can you post a larger sample of data?

 

[gaz_chops]

So you only want to see one "X", on the 1st occasion it increases by 1.5?

 

[MrSak87]

Sorry lunch break. On mine the X appears at the first 1.5 but then appears everytime after. So I want it to appear at 1.5, 3, 4.5, 6. I'll post a larger data sample