Home 
Search 
Today's Posts 
#1




Formula to scan group of four cells?
I'm struggling with this and would much appreciate some expert help please.
I want to scan down a column of decimals and if any group has four numbers which are all less than or equal to 0.6, then I want to copy a number from column A alongside the first of those four cells. Hopefully my screenshot clarifies this. https://www.dropbox.com/s/l7ik9km0re...oups.jpg?raw=1 I've also uploaded an example of a worksheet which contains at least one other group that qualifies. https://www.dropbox.com/s/34x1g5njn6...ple.xlsx?raw=1 I posted this yesterday on the Excel forum here https://www.excelforum.com/excelnew...ourcells.html but the suggested solution of the following formula copied down from L1 =IF(COUNTIF(K1:K4,"<0.4")=4, A1,"") did not work. Basically I just need to know how to construct a sort of 'combined IF'. Expressed at its simplest, how would I write IF(This AND That are true, then do X, else do Y), where This and That are values in separate cells? Terry, East Grinstead, UK 
#2




Formula to scan group of four cells?
Hi Terry,
Am Fri, 06 Sep 2019 10:31:58 +0100 schrieb Terry Pinnell: I want to scan down a column of decimals and if any group has four numbers which are all less than or equal to 0.6, then I want to copy a number from column A alongside the first of those four cells. Hopefully my screenshot clarifies this. https://www.dropbox.com/s/l7ik9km0re...oups.jpg?raw=1 Try: =IF(COUNTIF(OFFSET(K1,,,4),"<=0,6")=4,ROW(),"") or =IF(COUNTIF(K1:K4,"<=0,6")=4,ROW(),"") Regards Claus B.  Windows10 Office 2016 
#3




Formula to scan group of four cells?
Claus Busch wrote:
Hi Terry, Am Fri, 06 Sep 2019 10:31:58 +0100 schrieb Terry Pinnell: I want to scan down a column of decimals and if any group has four numbers which are all less than or equal to 0.6, then I want to copy a number from column A alongside the first of those four cells. Hopefully my screenshot clarifies this. https://www.dropbox.com/s/l7ik9km0re...oups.jpg?raw=1 Try: =IF(COUNTIF(OFFSET(K1,,,4),"<=0,6")=4,ROW(),"") or =IF(COUNTIF(K1:K4,"<=0,6")=4,ROW(),"") Regards Claus B. Thanks Claus, but neither seem to work. No results are placed in col L for any valid set of four successive cells, such as 0.2 0.2 0.2 0.5 which should enter 2 in L2 Or the example I showed in my screenshot. I think I could do it myself if you could tell me: " how to construct a sort of 'combined IF'. Expressed at its simplest, how would I write IF(This AND That are true, then do X, else do Y), where This and That are values in separate cells?" Something like this: IF(K1<0.6 AND K2<0.6 AND K3<0.6 AND K4<0.6, A1, "") I would then place the formula in L1, then copy that down the whole column. That should do it, yes? Best wishes, Terry, East Grinstead, UK 
#4




Formula to scan group of four cells?
Hi Terry,
Am Fri, 06 Sep 2019 14:49:43 +0100 schrieb Terry Pinnell: No results are placed in col L for any valid set of four successive cells, such as 0.2 0.2 0.2 0.5 which should enter 2 in L2 for me it works fine. Have a look: https://1drv.ms/x/s!AqMiGBK2qniTgeYC...Kr88A?e=hrQzpl If it doesn't work for you, your numbers are text formatted. Regards Claus B.  Windows10 Office 2016 
#5




Formula to scan group of four cells?
Claus Busch wrote:
Hi Terry, Am Fri, 06 Sep 2019 14:49:43 +0100 schrieb Terry Pinnell: No results are placed in col L for any valid set of four successive cells, such as 0.2 0.2 0.2 0.5 which should enter 2 in L2 for me it works fine. Have a look: https://1drv.ms/x/s!AqMiGBK2qniTgeYC...Kr88A?e=hrQzpl If it doesn't work for you, your numbers are text formatted. Regards Claus B. Hi Claus, I see why it didn't work! You entered my '0.6' as '0,6'. I assume you have Excel Options Advanced Decimal separator set that way, the German standard? Both now do exactly what I need, thanks so much ;) Best wishes, Terry, East Grinstead, UK 
#6




Formula to scan group of four cells?
Hi Terry,
Am Fri, 06 Sep 2019 16:28:14 +0100 schrieb Terry Pinnell: You entered my '0.6' as '0,6'. I assume you have Excel Options Advanced Decimal separator set that way, the German standard? Both now do exactly what I need, thanks so much ;) in Germany the decimal separator is a comma. But when the numbers are correct formatted this comma will in your system change tu period. Regards Claus B.  Windows10 Office 2016 
#7




Formula to scan group of four cells?
Claus Busch wrote:
Hi Terry, Am Fri, 06 Sep 2019 16:28:14 +0100 schrieb Terry Pinnell: You entered my '0.6' as '0,6'. I assume you have Excel Options Advanced Decimal separator set that way, the German standard? Both now do exactly what I need, thanks so much ;) in Germany the decimal separator is a comma. But when the numbers are correct formatted this comma will in your system change tu period. Regards Claus B. That's strange, as it doesn't get changed here. All my numbers in col K are decimals with one decimal point. I even tried formatting L the same, before pasting in your formula, but it made no difference. BTW, I'm puzzled why all the cells in L now have little green arrows (from either formula) which I see means there is an error in the formula. Presumably I cam ignore those? Best wishes, Terry, East Grinstead, UK 
#8




Formula to scan group of four cells?
Hi Terry,
Am Fri, 06 Sep 2019 17:03:26 +0100 schrieb Terry Pinnell: That's strange, as it doesn't get changed here. All my numbers in col K are decimals with one decimal point. I even tried formatting L the same, before pasting in your formula, but it made no difference. BTW, I'm puzzled why all the cells in L now have little green arrows (from either formula) which I see means there is an error in the formula. Presumably I cam ignore those? when the separator doesn't change when you open my file your formats are not correct in the WIndows control panel or you didn't have activated "Use system separators" in the Excel options. Sometimes it doesn't work to change text formatted numbers to real numbers. Then click in the columns header = Data = Text to columns = Fixed width = Finish. I guess the errors come from text formatted numbers. Regards Claus B.  Windows10 Office 2016 
#9




Formula to scan group of four cells?
how would I write IF(This AND That are true, then do X, else do Y)
IF(AND(THIS, THAT), X, Y) 
#10




Formula to scan group of four cells?
zvkmpw wrote:
how would I write IF(This AND That are true, then do X, else do Y) IF(AND(THIS, THAT), X, Y) Many thanks, that's simpler than I expected! This now works: =IF(AND(K1<=0.6,K2<=0.6,K3<=0.6,K4<=0.6),A1,"") And although it is obviously not as compact as your solutions, Claus, it has the big attraction for me of being more intuitive. (Using it so infrequently, I've forgotten quite a lot of the basics that I used to be comfortable with!) I think I can now also see how to reduce it to your second example. Re those messages, in this case hovering over the green triangle says "The formula in this cell refers to a range that has numbers adjacent to it." It's clearly not an error in this case. I've switched it off in 'File Options Formulas Error checking rules' by unchecking 'Formulas which omit cells in a region'. A good learning exercise; thanks both ;) Terry, East Grinstead, UK 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Need formula to add cells with same group  Excel Discussion (Misc queries)  
finding 1 formula result in a group of cells  New Users to Excel  
Scan group of cells and result based on condition  Excel Discussion (Misc queries)  
How can I use the COUNTIF formula on a group of specific cells?  Excel Discussion (Misc queries)  
how to change a formula for a large group of cells  Excel Worksheet Functions 