I have pivot table with 6 Columns
Month
Item Code
Unit Sales
Sales Dollars
Invoice Price
Price Increase
I have 12 months worth of data so each Item code shows up 12 times in the data table (once for each month)
The Price Increase field in my data set is a vlookup from a user input sheet that lists the codes and the user must input the % they would increase price.
Once the user inputs a value into the table by code that percentage is updated in my data set. So if they input 5% I will have 12 records that show 5% for that Item code in the Price increase field.
I want to use a calculated field to calculate my new invoice price. I set up a field called new Invoice and the formula is Invoice Price * (1 + Price Increase)
If the invoice price is $1.00 and they enter 5% it would be $1.00 * (1 +5%) = $1.05
My issue I am having is that if there are 12 values showing 5% it is calculating $1.00 * (1 + 60%) = $1.60 it is adding all of the 5% records together for that code.
Does anyone know how I could tell it to only grab the value only once, or is there some way I can tweak my vlookup to only show that increase once for each item. IE the first time code 123 shows up put the 5% value and all subsequent records of that item not show an increase?
Month
Item Code
Unit Sales
Sales Dollars
Invoice Price
Price Increase
I have 12 months worth of data so each Item code shows up 12 times in the data table (once for each month)
The Price Increase field in my data set is a vlookup from a user input sheet that lists the codes and the user must input the % they would increase price.
Once the user inputs a value into the table by code that percentage is updated in my data set. So if they input 5% I will have 12 records that show 5% for that Item code in the Price increase field.
I want to use a calculated field to calculate my new invoice price. I set up a field called new Invoice and the formula is Invoice Price * (1 + Price Increase)
If the invoice price is $1.00 and they enter 5% it would be $1.00 * (1 +5%) = $1.05
My issue I am having is that if there are 12 values showing 5% it is calculating $1.00 * (1 + 60%) = $1.60 it is adding all of the 5% records together for that code.
Does anyone know how I could tell it to only grab the value only once, or is there some way I can tweak my vlookup to only show that increase once for each item. IE the first time code 123 shows up put the 5% value and all subsequent records of that item not show an increase?
Last edited:
