Microsoft Office Forums Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached.
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-08-2016, 03:36 PM
 phillipsdp Windows 7 64bit Office 2010 64bit Novice Join Date: Nov 2016 Posts: 1
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached.

Hi -

I'm trying to build an output model similar to the one shown below, please see the following explanations to help answer:

Sample Source Data Explanation:

(I) The service date represents each transaction date for each customer
(II) The Unique ID represents a particular customer
(III) The product code input, (in this example 2 but can be 1, 2, or both) indicates whether or not the 1st threshold input (in this example \$300) should be deducted from the total cost when calculating net cost to me.
(IV) The total cost represents the total cost of each transaction

Other important considerations: The source data is sorted by service date and unique ID, so that each customers transactions are listed in the order in which they were incurred.

Sample Output Explanation:

* For each unique ID I need to calculate the net cost to me on each day of the year based on the inputs shown below: Explanation of Inputs:

(I) Product code- the product code can be either 1, 2, or both. In this example it is 2 and denotes when a customer transaction has a threshold amount that needs to be applied before subsidies are factored.

(II) 1st threshold- given the product code input of (1, 2, or both) the total cost of the transactions for the customer should be reduced by this amount until the total cost with the associated product code input has been exhausted. (Example: Unique ID 1x1's first transaction has product code 2 listed and a total cost of \$2,944.47 (far exceeding the first threshold, however there could be multiple transactions before the threshold is met, see Unique ID 3x3 from the sample output). The \$2,944.47 should be reduced by the 1st threshold amount of \$300 and the remainder should be further reduced by the subsidy amount (in this example is 25%) to show a net cost to me on 1/2/2015 of \$1,983.35. Further, the threshold input can be \$0 to \$400.

(III) Subsidy- this is the amount that should be used to reduce the total cost of each transaction. In this example when product code input equals 1 or when total cost of product code 2 has surpassed the 1st threshold the subsidy amount should be applied to reduce total cost. IMPORTANT: Subsidy can be expressed as either a percentage of total cost or as dollar value reduction of total cost.

Main Issues:
(I) When a unique ID has multiple transaction with a selected product code input before meeting the selected threshold amount.
(II) Once threshold is met further reducing the total cost by the subsidy.

Please note from the sample output, unique ID example 3x3 which encounters its first transaction on 1/1/2015 with a product code of 1 for \$5, because the product code is 1 only the subsidy is used to reduce the total cost of this first transaction. The second transaction occurs on 1/7/2015 with a product code of 2 for \$235, because this product code is equal to our input we must first reduce this claim by the threshold amount of \$300 (Note: this does not cause a negative balance instead it should be treated as the 1st step in surpassing our 1st threshold). The third transaction on 1/9/2015 with a product code of 2 for \$1,363.20 should be reduced by the remaining amount of our 1 threshold balance after the prior transaction on 1/7/2015 of \$65 (\$300 - \$235) and then further reduced by the subsidy input.
Attached Files
 Example.xlsx (13.8 KB, 9 views)

Last edited by phillipsdp; 11-08-2016 at 03:39 PM. Reason: Added Example Attachment
#2
11-09-2016, 12:53 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,584

Hi
http://www.mrexcel.com/forum/excel-q...d-reached.html
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post gebobs Excel 4 07-21-2016 08:26 AM caeiro01 Excel 1 10-25-2015 02:34 AM AUHAMM Excel 3 10-27-2014 09:11 PM GMorris Excel 9 08-20-2014 02:15 AM mlttkw Excel 1 12-06-2013 02:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:13 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top