• ## SUMIF Array Formula.... [SOLVED]

there's nothing in d2 on any of the sheets - what condition are you trying to apply?

As an aside, for numeric data, a simple, generalisable metrhod for conditional computation is as follows:

Conditional counting:

=sumproduct((range1=condition1)*(range2=condition2)*...*(rangeN=conditionN))

Conditional summing:

=sumproduct((range1=condition1)*(range2=condition2)*...*(rangeN=conditionN)*(sumrange))

• ## Problem writing formula

Hi.

In general, the way to get a formula to change it's refernece to reflect the addition of new data is to use a dynamic range as the cell reference. It is easy to set one up tht, for example, will always refer to the last 4 rows of data in a column.

So, for example, given the way you've got your data set uyp, the following will return the average for the last 4 entries on your sheet:

=AVERAGE(INDEX(Happ!D3:D59,COUNTA(Happ!D3:D59)-3,1):INDEX(Happ!D3:D59,COUNTA(Happ!D3:D59),1))

...not including the summary line you've got on row 60 of the data sheets.

If you could change the tab names so that they're all the same as the 'names' you've got on the summary stats sheet - it would make the final formulas easier to write (or at least to copy down...):

=AVERAGE(INDEX(INDIRECT(A4&"!D3:D59"),COUNTA(INDIRECT(A4&"!D3:D59"))-3,1):INDEX(INDIRECT(A4&"!D3:D59"),COUNTA(INDIRECT(A4&"!D3:D59")),1))

although all the indirects() might start playing with your file's performance.

• ## filtering [SOLVED]

Hi - welcome to the board!

Do you mean "Can I have the contents of one drop-down / filter list based on the results of another'? If so, then apply the following method (only one of many available):

"The method is as follows:

Enter in some column what follows:

{"USA";"FRANCE"}[ That's, enter USA in a cell, then FRANCE in the next cell down ]

Select these cells, go to the Name Box on the Formula Bar, and type COUNTRIES followed by enter.

Enter in a column next to COUNTRIES:

{"New York";"Pittsburgh";"Los Angeles";"Boston"}

Name this range of cells USA via the Name Box as described above.

Enter in a column next to USA:

{"Paris";"Nice";"Toulon"}

Name this range FRANCE.

Just to see how this works,

activate A1 in some worksheet in the same workbook;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=COUNTRIES

click OK;

activate another cell in the same worksheet, say, C1;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=INDIRECT(A1)

click OK.

Now you have two lists of which the 2nd depends on the selection from the 1st. "

....the quote is from Aladin Akyurek. You should be able to generalise it to other situations.

• ## checking to see if certain cells sum to 24 hours

a pivot table would be the simplest solution to adding up the hours by product.

• ## Graphing

also, a standard answer to the question "I want to do things with data label, ebut excel's charting options are too limited" is often "Download Rob Bovey's ChartLabeler Addin":

http://www.appspro.com/utilities/Labeler.asp

• ## Graphing

Hi - welcome to the board!

Kind of depends what you mean by 'label', what type of graph you're working with etc. post back with more details & the answer will emerge well before you starve.

• ## Pivot tables do not refresh contents when data is deleted an

Hi,

It is a "feature" of pivot tables to retain the classings. The usual response is that you have to re-create the pivot table. There is, however, a quicker work-around:

1) in the source data, rename the field that has changed. hit refresh
2) change it back to the original name, hit refresh.
3) pull the field back into te pivot table.

...not elegant, but qicker than building the whole thing again.

• ## Blank cell issues

Andy,

Just a (no doubt unnecessary) note of caution for the OP re your suggested:

=IF(ISERROR(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193)),0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

iserror() will mask all errors, including syntactical ones in the formula construction. In geeneral, error checks are best made as specific as possible:

if(error.type(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))=2,0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

• ## Blank cell issues

Hi - welcome to the board!

I took a look at the attachment. The answer to your question "Is my whole calculating basically flawed from the start?" is yes (from a design point of biew) - although if it serves it's general purpose I guess it's OK.

As you have discovered, the way you have set up your data is giving you headaches. Here is not the place to go into the elements of good spreadsheet design, but the folliowing links might prove of interest:

and

http://bsstudents.uce.ac.uk/le…ourse%20Material/smbp.pdf

although the latter is a rather large document. also, do a search on the web for "first normal form", read some of the articles & see how it might affect your design descisions.

more specifically, you are not getting div/0 errors because of the NN's and NA's. dividing a number by text values returns the #value! error. You're getting them because you're dividing by zero.

One way to get round this is to check for this condition before you perform the calculations:

rather than

=AVERAGE(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221)

try:

=if(sum(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221)=0,"Whatever",AVERAGE(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221))

...substitute "something" with whatever you want returned instead of div/0.

There are other alternatives, but given the 'complexities' of your spreadsheet design, there probably not worth discussiong until we know whether you intend to re-consider the way you're data is set up.

HTH

• ## Converting Text To Assigned Value?

butt in all you like - the more the merrier!

• ## Converting Text To Assigned Value?

vlookup()'s what you need. check it out in the help file & see example below:

the formula is

=VLOOKUP(E1,A1:B5,2,0)

HTH

• ## Converting Text To Assigned Value?

Hi - welcome to the board!

"will change text to an assigned value depending on what text it is "

...care to post back with the details about what you are actually trying to do - examples really help!

• ## Pivot Tables.

Hi - welcome to the board.

see the help file for:

'Sort data in a PivotTable or PivotChart report'

...it is possible to define a custom sort order for your pivot table.

• ## [Solved] Selecting variable range for formulas

"I was doing my usual referencing on a dynamic range from another sheet this week and I swear to God the ROW formula changed each time I looked at it in insert/names/define..... it was driving me nuts"

Chris,

You more-or-less always need to use absolute references in named formulas - is that what you mean?

• ## subtracting 2 values [SOLVED]

=sheet1!a1-sheet2!a1

...is that it?

• ## offset function

via U2U:

"No this is not exactly what I meant. In your example I would like to get the result 2
for entering the date 2/01/2001. "

...but you said you wanted a three column, 100 row offset? Anyway, if all you want to do is a "left lookup", simply remove the +10 from the formula I posted:

=INDEX(A2:D21,MATCH(F1,D2:D21,0),1)

• ## Sum when numbers are indicated by "X" [SOLVED]

one possible solution:

assuming that the only thing that can turn up in the "date" area is a X, insert another column that checks for whether a cross has been entered. something like:

=IF(SUM(LEN(D7:AH7)),"X","")

...array entered using control + shift + enter, not just enter, could be used to perform the check. you can then use a simple sumif() to get the result:

=SUMIF(AJ7:AJ16,"X",C7:C16)

obviously, you could hide the column that contatined the first calculation (AJ in this example)

• ## offset function

that said:

the formula is:

=INDEX(A2:D21,MATCH(F1,D2:D21,0)+10,1)

change the references to suit, and the +10 to +100 to get the appropriate offset.

...is that what you meant?

• ## picking points [SOLVED]

with the data as in you attached workbook:

=MAX(IF((A2:A2413>=0.3)*(A2:A2413<=1),B2:B2413))

...returns 279.04. The formula needs to be array entered using control + shift + enter, not just enter. You could also consider using dmax() - check it out in the help file.