7 Replies Latest reply on Dec 22, 2016 12:41 PM by Susan Hildebrant

# Struggling with Custom SubTotal using Min()=Max() expression

I am a novice user and have been successful creating custom subtotals previously, but I am stumped on how to modify this custom subtotal to get the desired result. The calculation works perfectly if there is more than one value in the column. However if the column contains only one value, the Total Billable 2 calculation does not execute properly. Here are the calculations:

Total Flag = If MIN([Project/Opportunity])=MAX([Project/Opportunity]) then 0 else 1 end

Billable Hours = IF [Contract Status]<>0 THEN IFNULL([Hours],0)  end

Total Billable 2 = IF([Total Flag])=0  then MAX([Hours]) else SUM([Billable Hours]) END

Here is an example of the result:

Apr 14 correctly calculates nothing since the Contract Status = 0%. April 15 correctly calculates 2 because Contract Status = 100%. June 22 incorrectly calculates 8 even though Contract Status is 0%. In this case, 8 is the only value in the column. Any ideas on how to fix? Adding zeroes everywhere is not an option.

Thanks in advance for any ideas.

Workbook attached.

• ###### 1. Re: Struggling with Custom SubTotal using Min()=Max() expression

Susan,

instead of using if min = max  to determine if it`s a row value or a total value, you can use  if first() = last(). (compute table down).

You can see the behavior in the attached  in the sheet Validate Data.

For the final view, you wont be able to use this new calculation for the Sorting of Project/Opportunity, so if you want to keep the existing sorting you can keep a copy of your old calculation just for the Sort.

Michel

• ###### 2. Re: Struggling with Custom SubTotal using Min()=Max() expression

Michel,

Thank you very much for your prompt reply and solution! I tried to use first()=last() but I am still learning how to make it work effectively. This is a great lesson for me.

Thanks again,

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

O: 508-683-3312| M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

INC 500/5000 Fastest Growing Company, 2010-2014

• ###### 3. Re: Struggling with Custom SubTotal using Min()=Max() expression

I guess I spoke too soon. I copied the calculations exactly and applied them to the worksheet. The results were not correct and I discovered that the ‘Total Flag (copy)’ was not calculating properly. I am clearly doing something wrong. Please advise.

Result:

Thanks,

Susan

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

O: 508-683-3312| M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

INC 500/5000 Fastest Growing Company, 2010-2014

• ###### 4. Re: Struggling with Custom SubTotal using Min()=Max() expression

first() and last()  being table calculations  you need to set the proper computing on them.

My guess is that you are computing Table Accross.

Right-click on the Total Flag (copy)  green pill  and select  Compute using - Table Down

• ###### 5. Re: Struggling with Custom SubTotal using Min()=Max() expression

Michel,

I corrected that and then added first() and last() into the view. I think I am almost there. (I find using a validation worksheet to be very valuable – thanks!)

At the topmost subtotal first() never equals last(). I can’t find a solution for that. What is the next step?

I am most appreciative of your help.

Susan

• ###### 6. Re: Struggling with Custom SubTotal using Min()=Max() expression

I will need to see your full workbook. Table calculations are linked to the view, so either you don't have the right computing or you added something in the view. But I can't answer your question with only the images.

ps: in 2 hours I am leaving for 2 weeks.

• ###### 7. Re: Struggling with Custom SubTotal using Min()=Max() expression

Hi Michel,

You last message gave me on the table calc to use “Restarting at” and it worked! Thanks again for all your help.

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

O: 508-683-3312| M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

INC 500/5000 Fastest Growing Company, 2010-2014