Excel question

Factual Questions
#1

I’m struggling with something in Excel, and the help files don’t seem to be getting me there. Perhaps I just don’t know what to search on.

I want to sum every other cell in a range.

Specifically, I have two columns for each date, and one for each product. One column records a quantity, the other a price. I want to put grand totals at the end, so I need one formula at the end of each row that sums the 1st, 3rd, 5th… columns in that row; and another formula that sums the 2nd, 4th, 6th…

I’m currently doing the very awkward =D4+F4+H4+J4…, which requires manually changing the formula every time I add a couple of columns for a new date.

Is there an easy way to do this?

Thanks!

#2

A bit kludgey, but it might work. Depends on your values.

The formula for the total would be something like =SUMIF(B4:I4,"<40000") The B4:I4 range will adjust properly when you insert columns. and…

The “<40000” criteria would screen out dates (which are really serial numbers in excel, so 40000 is July 6, 2009)

#3

Add another row, say row 5 for summing even numbere columns.
formula for in D5: =IF(MOD(COLUMN(D4), 2)=0, D4, 0)
copy formula in D5 across all columns in row 5 to the end of your data.
Put your sum formula for row 5 in row 4 or where ever you want the total.

Add another row, say row 6 for summing odd numbered column
repeat above but change =0 to =1.

#4

I’m not an excel guru, so what I would do is go out farther then you plan to need in the near future. Say, column 60 and 61 and enter all your formula there =SUM(A1, C1, E1…ZZ1) and the same for the odd columns. Then you can highlight that formula and paste it all the way down. Now, highlight all the columns in between the formula and your last currently used column and hide them. That way you won’t see them, but when you need to expand your worksheet you can just unhide the columns you need to.
Of course at that point, it might be easier just to adjust the formula each time. Remember, you only need to adjust one formula and then drag it down and it’ll automatically adjust it for each row.

#5

=SUM(IF(MOD(COLUMN([RANGE]),2)=0,[RANGE],0)) for even.
=SUM(IF(MOD(COLUMN([RANGE]),2)=1,[RANGE],0)) for odd.

Entered as an array formula where the grand total should be. gg.

#6

This seems like kind of an awkward data structure to begin with (i.e., having different data types in columns in the same sheet strikes me as kludgey).

Have you considered trying out a database instead? Or if you want to keep using Excel, maybe having the different data types in different sheets and using cross-sheet references where appropriate?

#7

I think there is a very strong chance that you want to rethink the structure of your data. It sounds like you may be better using a data table and a pivot table to do the analysis, but I’m struggling to visualise your current data structure from the description you’ve given. Can you give us any more detail?

#8

Palooka’s formula is the direction I was heading, although I agree with Hunter Hawk. When you have this kind of a question, it usually indicates your data organization could be better. The ideal case is to have one row for each individual transaction and one column each for date, product, quantity, price. Then report on it using a pivot table.

I participate actively on a couple of Excel boards and people often ask about the mechanics of how to do something very detailed, and the answer is to step back and solve the overall problem using a different approach.

#9

My apologies for leaving the thread for a couple of days there. I wasn’t at work and didn’t have the spreadsheet in front of me.

I do know that this is an awkward way to process the data, but it seems to be the best way to view and enter the data. The spreadsheet is for an audit, and the data needs to be all on one sheet in a clear and easy-to-read format. I’m doing something like this:



      January 5   March 14   April 23 ...
      QTY  COST  QTY  COST  QTY  COST ... 
ITEM1   3  6.00    2  4.04    4  7.92
ITEM2             12 48.48
ITEM3   1 19.93               1 19.96
      --- -----  --- -----  --- -----
        4 25.93   14 54.52    5 27.88

If I put the quantities and costs in separate tables, it would be hard to cross-reference. Summing down (total qty/cost for January 5 purchase) is easy. Summing across (total qty/cost for all item1’s purchased) is the pain.

I know this cries out for a database or 3-D spreadsheet approach, but there’s really not enough data to justify designing and building a custom database for it.

The dates aren’t in the individual rows (which kills K364’s solution).

I tried Palooka’s approach and can’t get it to work. If I put…

=SUM(IF(MOD(COLUMN(E8:AA8),2)=0,E8:AA8,0))

I get zero. If I change the =0 to an =1, I get the sum of all columns, odd or even.

For the moment, I went with the…

=F8+H8+J8+L8+N8+P8+R8+T8+V8+X8+Z8…

approach, but it requires modifying both formulas and copying down in two different tables (I’m doing this for two different product lines).

I’m still puzzled.

#10

To understand Palooka’s approach, you have to notice that he said it’s an “array formula.” This is an advanced feature of Excel that allows one formula to make the same calculation over an entire array of cells, and is effectively iteration.

But it requires one little extra move to enter it. Once you type the formula in, you have to hit CTRL-SHIFT-ENTER. If you just hit ENTER, you get a plain vanilla formula.

You can achieve the same effect with a formula you’ve already written by hitting F2 to edit the formula then CTRL-SHIFT-ENTER.

If you do this properly, you will see

{=SUM(IF(MOD(COLUMN(E8:AA8),2)=0,E8:AA8,0))}

in the formula box. But you can’t just type in brackets, that won’t work. You have to use CTRL-SHIFT-ENTER.

But I’m still advocating a change in format. If you do it like this (same data as your example):



Date      Item  Qty Cost
1/5/2010  ITEM1  3  6.00
3/14/2010 ITEM1  2  4.04
4/23/2010 ITEM1  4  7.92
3/14/2010 ITEM2 12 48.48
1/5/2010  ITEM3  1 19.93
4/34/2010 ITEM3  1 19.96

then you can use a pivot table, which is much more flexible and powerful than anything you can do messing around with formulas.

#11

Excel help isn’t very good on the array formula topic. Let me explain further. This formula is the equivalent of:



=SUM(IF(MOD(COLUMN(E8),2)=0,E8,0),
     IF(MOD(COLUMN(F8),2)=0,F8,0),
     IF(MOD(COLUMN(G8),2)=0,G8,0),
     IF(MOD(COLUMN(H8),2)=0,H8,0),
     IF(MOD(COLUMN(I8),2)=0,I8,0),
.....
     IF(MOD(COLUMN(AA8),2)=0,AA8,0))
#12

This. Your data should look like this:



DATE     ITEM   QTY   COST
1/5/10   Item1   3    6.00
1/5/10   Item3   1   19.93
3/14/10  Item1   2    4.04
3/14/10  Item2  12   48.48
4/23/10  Item1   4    7.92
4/23/10  Item3   1   19.96

Edit: I see this is exactly what CookingWithGas came up with as well.

That alone lets you sort by any of the different data points without them being split out. Using a pivot will allow you to run pretty much any sort of report you want since your data is now clean. Example:



ROW               1/5/10    3/14/10    4/23/10   TOTAL
Item 1
   Sum of Qty       3         2          4         9
   Sum of Cost      6         4.04       7.92     17.96
Item 2
   Sum of Qty                12                   12
   Sum of Cost               48.48                48.48
Item 3
   Sum of Qty       1                    1         2
   Sum of Cost     19.93                19.96     39.89

Total Sum of Qty    4        14          5        23
Total Sum of Cost  25.93     52.52      27.88    106.33
#13

In case it’s not clear, that last table is a quick pivot chart I ran from the “clean”* data CWG and I came up with.

*Clean means that each row should contain just one complete piece of information, not more - your chart has each row displaying 3 pieces of data (with more to be added in the future). That’s inefficient and requires constant tinkering. Keep it clean.

#14

Bingo

That’s exactly what I needed. Thank you very much!

I understand what you’re saying. Really I do. But my definition of “clean” involves not entering redundant/extra data. With your solution, I have to type the name of the item each time it’s ordered, not just once per spreadsheet. These product names aren’t 5 characters long: more like 30 or more. I could create short product codes, but then I’d have another table to correlate product codes to product names, another level of obscurity in the main table, and another step (looking up the code) in data entry.

That seems to be a perfect approach if I’m creating a full application with data-entry forms and a relational database, but for my purposes here, it just seems like overkill.

NB: I am considering expanding this spreadsheet when the audit (my current focus) is over and using it as an management tool. If I do so, I will almost certainly switch to a variant of the format you’re using, because it gives me more options for analysis, such as spotting price anomalies, and cross-referencing data with reports pulled from my point-of-sale/inventory control system.
Thanks again, everyone. I got there slowly, but you taught me something new and solved my immediate problem. Life is good.

#15

Then create a quick drop-down list. It’s really easy to do.

#16

Hmmm. Never built drop-down lists in Excel. I’ll go research that.

Thank you.

#17

I have the same kind of situation as you Gary. I am a teacher and use excel to record teh results of assessments. A list of names is down the left and the columns alternate raw score and percentage. I know that there are other ways of presenting the data but this is simple and works well.
A fairly common calculation is to calculate the mean percentages for a term or semester. I usually use a formula like =average(c1,e1,g1,i1). Not too clunky but then I am just usually averaging half a dozen values at the most.
I might try the array formula suggested by Palooka just to improve my excel knowhow. Ditto for drop down lists.
Thanks.

#18

Not to beat a dead horse, but merely to spread the good word - learn how to do a pivot chart. It will expand your Excel utilization far beyond that of any other task/formula available.

#19

Look up “data validation”. One option is a list, one option for that is a drop-down list. It really is easy to set up but will take you a couple of minutes to figure out the first time.

#20

If each column has a header of QTY or COST, as shown in the example, there is a much easier way: The following is a solution for four QTY/COST pairs, beginning in B2 and ending in I2.

=SUMIF(B$2:I$2,“QTY”,B3:I3)
=SUMIF(B$2:I$2,“COST”,B3:I3)

These can be copied down the rows and B3:I3 will automatically change to each row to which it is copied.