Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 6th 19, 10:31 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 149
Default 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/excel-new...our-cells.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   Report Post  
Old September 6th 19, 10:43 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old September 6th 19, 02:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 149
Default 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   Report Post  
Old September 6th 19, 02:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old September 6th 19, 04:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 149
Default 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   Report Post  
Old September 6th 19, 04:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old September 6th 19, 05:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 149
Default 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   Report Post  
Old September 6th 19, 05:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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   Report Post  
Old September 6th 19, 05:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 152
Default 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   Report Post  
Old September 7th 19, 07:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 149
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need formula to add cells with same group brantty Excel Discussion (Misc queries) 2 August 26th 09 02:11 AM
finding 1 formula result in a group of cells Richdg New Users to Excel 6 June 1st 09 06:44 PM
Scan group of cells and result based on condition Manan Excel Discussion (Misc queries) 2 May 8th 08 06:52 PM
How can I use the COUNTIF formula on a group of specific cells? [email protected] Excel Discussion (Misc queries) 3 August 3rd 06 12:58 PM
how to change a formula for a large group of cells Jen Excel Worksheet Functions 1 October 2nd 05 04:42 PM


All times are GMT +1. The time now is 02:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017