Criteria based calculation help.

Originally Posted by Cutter

You apparently don't realize what COLUMNS(X:X) does.

It returns the number of columns in the given range, which in your case is Column X
So it will ALWAYS = 1.

I assume you want to do the calculation if the value in a cell within Col X = 1?
If so, replace COLUMNS(X:X) with that cell reference.

But your unexpected calc result would not be caused by the IF condition you gave it. You'll have to check your formula or data for the cause of that.

Compare the brackets used in your first formula with what you tried in the second. Enclose everything left of the division symbol.

Cutter, can you help me write this? Im a tadd bit confused. Thanks

Originally Posted by Cutter

If the first formula you show in your original post is working correctly re-use it but place it within an IF() function so:

=IF(X?=1,($B$6*'Detail Service Calculator'!$AA$15-(SUM('Detail Service Calculator'!$S:$S,)))/($B$6*'Detail Service Calculator'!$AA$15),"")

Replace the ? with the cell Row # you want to check for 1, so if it's X1 you want to watch replace X? with X1.

But that's assuming your initial formula does give you the correct result which is what you implied.

EDIT: you have an orphan comma in your original formula just before the closing brackets left of the division symbol so remove that.

The formula did not take give me the desired result...

Attached is the file for your reference.

Please refer to cell B9. C9 is where your formula would go.

http://www.4shared.com/file/QgqZG4Yb...AIN_v1018.html

Thanks in advance!

Unless you attach it directly to the forum I don't open a file.

But it isn't necessary if you answer one question: Does the formula that you originally posted give you the expected result?
If not, provide a pasted copy of the formula you used to get the expected result along with the cell address that you want to check for a value of 1.

Please advise on how to go about attaching a file through this forum.

Originally Posted by Cutter

Unless you attach it directly to the forum I don't open a file.

But it isn't necessary if you answer one question: Does the formula that you originally posted give you the expected result?
If not, provide a pasted copy of the formula you used to get the expected result along with the cell address that you want to check for a value of 1.

Yes, the first formula does result in a correct outcome.

You got a blank result because X:X can not equal 1.

It needs to be a cell reference, not the whole column.

I'm looking at your file and trying to figure out what it is you're trying to achieve.

I was just looking at cell B8. Why do you have a SUMPRODUCT() function there?

It is a simple SUM() of the 12 columns to the right.

SUMPRODUCT() is a resource hog in that it really slows down calc times.

Also in cell 'Detail Service Calculator'!$AA$15 you have an unnecessary SUMPRODUCT(), it is a SUM() of the 12 cells above it.

Avoid SUMPRODUCT() wherever you can. Also avoid summing whole columns and having more than one cell do same calc. Have one cell do the calc and then refer to that cell.

Are you trying to get a result in each cell in range C9:N9 which?

If so, will the B6 value hold true for each month?

As long as B6 is to be used throughout couldn't you use:

in C9: =(B6*'Detail Service Calculator'!$AA$3-(C8*24))/(B6*'Detail Service Calculator'!$AA$3)
It results in 99.89% Would that be right?

Another simplification you could make is in that B6 calc.

Instead of:

=COUNTA('Inventory Detail'!A:A)+(-1)

this is same result:

=COUNT('Inventory Detail'!A2:A756)

with less calc

You could make it easier still by naming that range so it becomes:

=COUNT(name)

In C7 you have:

=SUMPRODUCT(COUNTIFS('Detail Service Calculator'!$G:$G,{"1-serv call","n-systems","3-recall","4-more time","6-follow up","7-part call","8-high part"},'Detail Service Calculator'!$X:$X,1))


But it seems as though you could use:

=COUNTIF('Detail Service Calculator'!X3:X1108,1)

same result

If acceptable you can change it to:

=COUNTIF('Detail Service Calculator'!$X3:$X1108,COLUMN()-2)

and drag across

Doing this made a noticeable difference in calc times

Originally Posted by Cutter

In C7 you have:

=SUMPRODUCT(COUNTIFS('Detail Service Calculator'!$G:$G,{"1-serv call","n-systems","3-recall","4-more time","6-follow up","7-part call","8-high part"},'Detail Service Calculator'!$X:$X,1))


But it seems as though you could use:

=COUNTIF('Detail Service Calculator'!X3:X1108,1)

same result

If acceptable you can change it to:

=COUNTIF('Detail Service Calculator'!$X3:$X1108,COLUMN()-2)

and drag across

Doing this made a noticeable difference in calc times

I cannot do so, because this was mock data that i filtered. The actual data has numerous call types in which i must filter. Thanks brother!

Cutter! Indeed you are a (Valued Forum Contributor) to this forum. I applaud your diligence in helping me figure this out and going the extra mile to simplify some of my calculations. This is something I have been trying to tackle for some time now.

Once again thank you very VERY much. Have yourself a spectacular weekend.

On the Detail Service Calculator sheet in columns A,E & G you have numerous OFFSET() functions that are doing nothing except returning the value of the referenced cell.

For example in cell A3 you have:

=OFFSET(DetailCompany!$C3,0,0,1,1)

this is exactly the same as:

=DetailCompany!$C3

except that the OFFSET() function is volatile so every time a calculation is made anywhere this cell gets calculated even if it isn't affected by the change that triggered the calculation.

The formulas in columns B,C and D will always return "No Data" because your VLOOKUP() functions are always going to return an error. You have your lookup values as entire columns!

I'm afraid your workbook needs a major overhaul.

Originally Posted by Cutter

On the Detail Service Calculator sheet in columns A,E & G you have numerous OFFSET() functions that are doing nothing except returning the value of the referenced cell.

For example in cell A3 you have:

=OFFSET(DetailCompany!$C3,0,0,1,1)

this is exactly the same as:

=DetailCompany!$C3

except that the OFFSET() function is volatile so every time a calculation is made anywhere this cell gets calculated even if it isn't affected by the change that triggered the calculation.

The formulas in columns B,C and D will always return "No Data" because your VLOOKUP() functions are always going to return an error. You have your lookup values as entire columns!

I'm afraid your workbook needs a major overhaul.

B<C<D are ok, they are reading that because the linked data isnt present on this demo version of my work.

On the Offset formula, you are 100% on the money. I will make those changes over as you suggested. Thanks again!