Simplify a sum.if - and divide formula

Hi,

Is there a way to simplify my formula..?

Formula used (please see attachment):

=IF(TODAY()>A22,SUM.IF(Table4[Date],"<="&A22,Table4[Value2])/(SUM.IF(Table4[Date],"<="&A22,Table4[Value1])-SUM.IF(Table4[Date],"<="&A22,Table4[Value3])),"")

Right now my formula uses 3 times the 'SUM.IF(Table4[Date];"<="&A22' part and it gets the job done but I'd like to clean it up, but can't figure out how, if at all possible.

Many thanks in advance....

Mike

I would like to calculate a cumulative percentage based on a service level agreement, where value2 is answered calls within servicelevel, divided by value 1 (offered) minus value3 (abandoned within servicelevel).

For your second question: I don't know, I do however work in London on a regular base, but now I'm not...

Originally Posted by blak9

Is there a way to simplify my formula..?
it gets the job done

If it ain't broke, don't try to fix it!
To try and simplify it (if at all possible), you would need to replace the repeated part that you want to remove with a series of less efficient arrays capable of performing the same task.

Thanks, I was hoping some kind of sumproduct formula could do the trick, maybe something like:

=SUMPRODUCT(--(Table4[Date]<=A22);Table4[Value2]/(Table4[Value1]-Table4[Value3]))

It will work with the correct syntax, but the results will not be the same.

With your sumif formula, for date 02/04/19, the result is (58+52)/((65+68)-(2+10))

With sumproduct for the same date, it would be (58/(65-2))+(52/(68-10))

To achieve the same result as your formula with sumproduct would require a formula far less simple than your existing one.

Additionally, sumproduct will take longer to calculate than sumif with large volumes of data.

With experience, you will discover that the simplest formula is not always the shortest one, or the most pracrical.

Originally Posted by canapone

if the two calendars don't skip any date

The first one does.

I think that this is the shortest formula that will return the same results as the original, whether or not it is cleaner / simplified is a matter of opinion.

Formula:

Please Login or Register  to view this content.

Noting the comments in the sample file,

SHEET 1: Table can contain several years of data (so I'd rather not use any formulas in this table)
SHEET 2, always limited to 1 month

personally, I would stick with the original sumif formula for efficiency.

Thanks a lot, I'll stick with my original formula, but I still learned something in the process of studying your answers...