Solved

Excel operating on every other column

Posted on 2013-01-15
12
467 Views
Last Modified: 2013-01-17
I have these data sheets we import and must maintain the layout in which they come in. The issue is that the columns are sequenced so that we need to work with every-other column.

For example, the columns are labeled NEAR and then FAR alternatively as so:                                    


      NEAR FAR NEAR FAR NEAR FAR

I am trying to count the number of results that are less than 0 in either the NEAR Columns or the FAR columns.

I have tried to create a named range for the "NEAR" (and "FAR ") values but the formula will not accept the named range.
                                                                  
                                                             
=COUNTIF(NEAR,"<0")      #VALUE!                                                                  
=COUNTIF(FAR ,"<0")      #VALUE!                                                                  
                                                                        
Named Ranges            
                                                            
NEAR=Sheet1!$B$2,Sheet1!$D$2, Sheet1!$F$2,Sheet1!$H$2, Sheet1!$J$2,Sheet1!$L$2                                    
FAR = Sheet1!$C$2,Sheet1!$E$2, Sheet1!$G$2,Sheet1!$I$2, Sheet1!$K$2,Sheet1!$M$2


Is there a way to count the values for just the NEAR data (or the FAR data) that is less than 0?
0
Comment
Question by:Zipbang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 7

Expert Comment

by:leptonka
ID: 38779054
Hi,

Name your NEAR-FAR header as "header" and name the data part below this header as "data" so you can use this formula to count data below 0:
=SUMPRODUCT(--((header="NEAR")*data<0))
=SUMPRODUCT(--((header="FAR")*data<0))

Hope it works.
Cheers,
Kris
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38779093
If you have Excel 2007 or later you can also do that with COUNTIFS

=COUNTIFS(header,"near",data,"<0")

regards, barry
0
 
LVL 7

Expert Comment

by:leptonka
ID: 38779121
barry, I assumed "data" contains more than one row.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:NBVC
ID: 38779168
Here is a way using the Named Range for non-contiguous cells..


=SUMPRODUCT((LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))>0)*(LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))))
0
 
LVL 24

Expert Comment

by:Steve
ID: 38779200
The following entered as array formula (with [ctrl]+[shift]+[enter]) will count up odd columns between A and L above 0:
=SUM((A2:L2>0)*(MOD(COLUMN(A2:L2),2)))

This will do even columns
=SUM((A2:L2>0)*(MOD(COLUMN(A2:L2)+1,2)))
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38780035
>barry, I assumed "data" contains more than one row.

Hello Kris

Yes, you are correct - my suggestion won't work if the data is multiple rows.....but in the question Near and Far are all cells within row 2. Is that just an example, Zipbang, or is that the extent of your data?

>=SUMPRODUCT((LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))>0)*(LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))))

Hi NBVC,

Isn't that giving a sum of the positive cells?

You can get a count of values < 0 using that approach with

=SUMPRODUCT(((LARGE(Near,ROW(INDIRECT("1:"&COUNT(Near))))<0)+0))

or perhaps

=INDEX(FREQUENCY(Near,-0.00000000000001),1)
0
 

Author Comment

by:Zipbang
ID: 38782321
The data is in multiple rows (varies in how many) and multiple columns (again, varies as to how many)

thank you,

Chris
0
 
LVL 24

Accepted Solution

by:
Steve earned 325 total points
ID: 38782410
The formulas below entered with [ctrl]+[shift]+[enter]:
 
=SUM((A2:Z20>0)*(MOD(COLUMN(A2:Z20),2)))
will count all cells in the odd columns (ACEGI...) greater than 0 in the range AtoZ rows 2to20.

=SUM((A2:Z20>0)*(MOD(COLUMN(A2:Z20)+1,2)))
will count all cells in the odd columns (BDFHJ...) greater than 0 in the range AtoZ rows 2to20.

The formula can be changed to suit the dize of the data, but not to include whole columns or rows
0
 
LVL 7

Expert Comment

by:leptonka
ID: 38782447
Using the formula by The_Barman with SUMPRODUCT you will not need [ctrl]+[shift]+[enter].

Or if you would like to summarize by the text in your header (A1:Z1), you can do it as I mentioned above:
=SUMPRODUCT(--((A1:Z1="NEAR")*A2:Z20<0))
=SUMPRODUCT(--((A1:Z1="FAR")*A2:Z20<0))

Cheers,
Kris
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38782512
Hi Barry,

You are correct, I carried it too far and summed the positive instead of count the negatives....

thanks for catching that.
0
 

Author Closing Comment

by:Zipbang
ID: 38788606
Thanks to all for very good responses.  I must go with The_Barman as that is what I ended up using and it works perfectly.

Thanks again.

TCC
zipbang
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38788940
FYI,

The reason why Barry and I used the formulas we did, is that we (at least I) thought that the Named Range was important for you.  With the Named Range and our formulas, the data does not necessarily have to be distributed in equidistant columns.....
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question