Plot first 4 digits if it is in given range

[Sekars]

Hello Friends

Please find the attached file (Plot first 4 digits if it is in given range 250516.xlsx).

I need to plot the cells B3:B79 if the conditions met for the first four digits.

Case 1 - If the first 4 digits are > 364 and < 628 plot in light green cells D9:D25
Case 2 - If the first 4 digits are >/= 628 then plot in light yellow cells F9:F63

thanks in advance

[FDibbins]

I did this using a helper column (for the blue, you should be able to replicate it for the yellow)...
1st know that the contents of A are not values, they are text, and 1 text cannot be greater then another, so the helper converts the text to a number, then sets up a progressive count, which I use to pull in the data

I used I, but you can use what you want...
I2=IF(AND(--LEFT(A2,4)>364,--LEFT(A2,4)<628),I1+1,I1)
copied down.

Then to pull in the data...
F9=IFERROR(INDEX(A:A,MATCH(ROWS($A$1:A1),$I:$I,0)),"")
copied down
C13 0364-7MS00A does not qualify because it is = 364, and you said >

[samba_ravi]

c9

Please Login or Register  to view this content.

E9

Please Login or Register  to view this content.

Try this and copy towards down

[Sekars]

Hello Ford

thanks for your formulas,

is there any way to avoid helpers (May be by using this <=VALUE(LEFT(A2,4))>).

thanks again

[FDibbins]

Siva, nicely done I thought about using that ARRAY, but thought that if the OP has a lot of data, that slow things down, so I didnt bother. Nice to see what it looks like

[azumi]

wrong post

[Crooza]

Here's an array in C9 and drag down

remember CSE

Please Login or Register  to view this content.

You can follow the same logic for the other column

[Sekars]

Hello Ford, Siva and Crooza

Thanks for your formulas.

Thanks again

[FDibbins]

Happy to help and thanks for the feedback