Using Name Ranges To Group Customers Into Different Pricing Brackets
Aug 27, 2009
I have a worksheet that needs customers split into different price bands
here we go
I have 13 customers all from the same group (a)
1 customer in group (b)
1 Customer in group (c)
& 80 independant customers (d) worksheet only has 9 (1,2,3,4,5,6,7,8,9)
Column a is a list
D17 is the drop down list
D21 is the starting price
C2:D5 is the pricing multiple to be applied
What I am trying to achieve is when an independant company is chosen it defaults to d for pricing.
View 10 Replies
ADVERTISEMENT
Apr 30, 2014
How to use conditional formatting in Excel 2007 (). I'm creating a spreadsheet to track my group's timeline for completing strategy meetings with our customers. When we have a meeting, I will enter the date of that meeting in a specific cell. In a successive column(s), I'd like for a cell(s) to turn a specific color based on the length of time from the last meeting (or if there is no date for a meeting entered - i.e. no initial meeting has been set).
View 9 Replies
View Related
Feb 27, 2009
I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula:
=AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
View 4 Replies
View Related
Jun 4, 2013
Every month we have to reconcile our checks and then enter them into our application. We have an option in our application that we can do Check Number ranges. We are trying to find an away to have excel to put the checks in ranges either by a formula, a micro or a pivot tables. We have over 700 checks each month to reconcile.
View 9 Replies
View Related
Oct 3, 2012
I starting to develop a macro for grouping specific information together. part of it I need to group data based on date ranges. My plan was to, in one sheet label the start and end ranges by month (not every range starts and stops at the beginning and end of the month)
For July I would have 7/1/2012 in one cell and in another 7/28/2012
For august I would have 7/29/2012 next to it 8/25/2012, etc
I need to take those ranges and in my FY2013 sheet column I where all of my dates are listed, in the next column J if the date says 7/1/2012 to label it July, if date says 7/30/2012 to label it August, etc.
View 1 Replies
View Related
Apr 28, 2014
Cells in row C have numbers (number of days between date a and date b)
I want to group the days so I can run a usable pivot table as follows:
Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +
My attempt is as follows but only give two results and not 4?
=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))
View 3 Replies
View Related
Jul 31, 2014
Ive been given the data for bond pricing for two tenures i.e. 15 years and 20 years . I needed to find the average pricing for 16.5 years , whether excel is able to compute such pricing?
View 5 Replies
View Related
Oct 12, 2009
When i open a new thread, so here i am doing so at the advice of him. The message below is specific to him to reply to but for anyone else that might want to help me with this set of formulas to resolve my worksheet headache and lack of skills in excel. Please feel free to have a look at the worksheet i posted and the comments in each color coded cell.
View 8 Replies
View Related
Dec 15, 2011
I have a zone matrix I'm working on. How can I have a formula match the state and return the numeric value assigned to the state?
Say I enter WA in cell "G5" and in column A I have the states and column B has the numeric value assigned. I need to to match and return the value.
So if "OR" is entered the returned value is "1"
StateZone
WA1
OR1
ID1
[Code]....
View 2 Replies
View Related
Aug 20, 2008
I receive hundreds of part numbers to quote daily. I send out RFQ's to my suppliers and they respond with pricing. I gather all the data into a similar excel sheet below and assign the wining supplier.
in automating a task that will compare the group of data for best pricing and populate a "WIN" code in column "A" if that supplier was chosen for the that qty pricing.
Here is the excel example.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI22=ABCDEFGHI1Scenario #1 - Supplier chosen for best pricing based on column "E" 2WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST33 123ALL90110510107.54 123FO3511258107.755 123RO501958.51086WIN123AR651858.751087 8 9Scenario #2 - Supplier chosen for best pricing based on column "G" 10WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST311 123ALL90110510107.512 123FO3511258107.7513WIN123RO501958.510814 123AR651858.7510815 16 17Scenario #3 - Supplier chosen for best pricing based on column "I" 18WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST319WIN123ALL90110510107.520 123FO3511258107.7521 123RO501958.510822 123AR651858.75108Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 10 Replies
View Related
Apr 24, 2012
I have a spreadsheet of prices from vendors for products that my company sells that is updated daily. The sheet contains several products with pricing from multiple vendors and multiple locations. (Example: paper, pens and staples sold by Staples, Office Max and Office Depot, out of NY, NJ and CT.) Many of our customers use a set vendor for most days but occasionally call us to see if we can beat their regular vendor's price. We would like to be able to email them with our pricing every day so that we can have their business every time our pricing is best without them having to call us.
We have a sheet of customers with contact info and common products purchased put together already for a mail merge. At this point, we manually enter the best price from our vendors and a column calculates the price that would go to our customer based on markup and taxes, etc. and we send it out using the mail merge tool. However, our pricing sheet is not ready until about 8 am and the mail merge sheet is not ready until at least 10 am due to the large (and growing) number of customers on it. Many of our customers place their orders first thing the morning so we're losing business.
way to use VLOOKUP to draw from the pricing sheet as soon as it's finished and copied into Sheet2 of the mail merge sheet. The problem is that each customer has different parameters. We are in New England so we have vendors in several states. Ideally, we need to be able to provide a price for a RI customer based on the lowest price for their product out of one of our RI vendors and provide a price for an MA customer based on the lowest price for their product from any vendor in any NE state. The lowest price is different every day and the locations all have different prices. I'm also a VLOOKUP newbie so I don't know everything that it can do. Currently, I only use it to calculate shipping based on a pricing sheet that is always the same. I choose the product and all other data from the static price sheet comes over. I'm not sure how to create a sheet using VLOOKUP for a sheet that changes every day or one that requires more than one drop down. I'm not sure how to create the second drop down.
Also, at this point, the mail merge generates multiple emails for customers that purchase 2 or more products from us. Is there a way to include an array in a mail merge so that their email contains all products they use?
I'm not sure if it makes a difference, but each product is not sold out of each location and each vendor so the number of lines per product is different for each product. However, the number of lines per product is always the same day after day and if any vendors or locations are added or removed, we can tweak our merge accordingly. So basically the first 25 rows are always for product A and column A always reads Product A for rows 2-26; the columns that change are the vendor and location columns and of course the price column.
View 2 Replies
View Related
Aug 21, 2008
i have many tables for many carriers with the same format, and i need to create a unified table of them, the tables look like this
-------A---------------------B-----------------------C---------
----Destination----------Country_Code-----------Price_Per_Minute
1------UK-------------------4479-------------------0.256-----
2------USA------------------1148------------------0.300------
3------Jordan----------------9626------------------0.899------
4------India------------------9198------------------0.345------
so let us consider that these are the prices for X company and i have another ten tables for other 10 companies, what i need to do is to create a unified table that includes all the companies prices for all the destination depending on the Country_Code as it could be considered as a primary Key and without any duplications and to include the all the data in all the tables as some destinations and prices are not available in all the tables, so the tables contains different data, the unified table should look something like this
-------A----------------B----------------C------------D---------E------
----Destination-------Country_Code-----X Price-----Y Price----Z Price----
1------UK-----------------4479-----------0.256-----0.254------0.356----
2------USA---------------1148------------0.300-----0.876------ ---
3------Jordan-------------9626-----------0.899------0.987------0.324---
4------India---------------9198-----------0.345----- -------0.564--
View 6 Replies
View Related
Apr 23, 2008
The phone call is charged at 0.57 for the first 60 seconds, thereafter 0.0095 per second. Determine cost if duration (in seconds) is available?
AND
The phone caal is charged at 1.654 for the first 60 seconds, thereafter 0.827 for every 30 seconds.Determine cost if duration (in seconds) is available?
View 7 Replies
View Related
Nov 27, 2013
We are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.
For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:
1 units @ $3 per unit
5 units @ $2 per unit
10 units @ $1 per unit
What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.
Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.
View 2 Replies
View Related
Mar 27, 2009
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
View 5 Replies
View Related
Mar 17, 2014
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies
View Related
Nov 12, 2009
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
View 9 Replies
View Related
Feb 10, 2014
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies
View Related
Apr 1, 2014
I have huge data of income of employees.
In column "A" i have names of employees,
in column "B" i have income of last month and
in column "c" i have income of current month.
In column "D" i have calculated difference between two month's income(C1-B1).
Now based on values in column D i want to find names of top 5 gainers and top decliners (i.e. i want names of top 5 people who are having highest value in column "D"). I don't want to use pivot and sort, I want these names by using any formula.
Its working perfectly. But now I have added one more column in the beginning as country names. Now i want to make a dashboard so that i will create a drop down with country names and country i will select top gainers should come with ur given formula.
View 1 Replies
View Related
Apr 13, 2012
I am working on a project and I have a list of customers. I need to calculate how many customers we had in week 1, week 2 etc of year. This would allow me to draw a beautiful chart of our company. How would you calculate this? Here is some data:
ABCDEF1February 2, 2011
Week1232February 15, 2011
Customers???3March 15, 2011
4April 15, 2011
5April 15, 2011
[code].....
View 4 Replies
View Related
Jan 17, 2013
how to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
View 2 Replies
View Related
Aug 9, 2006
My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
View 9 Replies
View Related
Jan 22, 2014
Making a customer analyse and now I want to know which customers are resposible for 50 % of my revenue.
View 4 Replies
View Related
Apr 15, 2009
I have the following sheet, each time a customer buys a savings product they are put onto the list, the same customer can appear as many times as they like on the list is they buy more than once, buys product on 1st of april then another product on the 2nd of april they appear 2ce on the sheet.
I have done the forumla to sum the amount of times they appear on the sheet ( total number of sales ) but I need to work out the total comission per person.
View 3 Replies
View Related
Nov 12, 2012
Code:
Customer 1 5,000
Customer 2 10,000
Customer 3 6,000
Customer 4 4,000
Customer 5 3,000
If I have a Table as above, how do I return the Top 3 Customers in 3 cells?
I can get the Values with
=LARGE(Table1[[#All],[Sales]],1)
=LARGE(Table1[[#All],[Sales]],2)
=LARGE(Table1[[#All],[Sales],3)
How would I return the Customer Names?
View 4 Replies
View Related
Oct 22, 2008
I have exported a report out of Quickbooks with all my customers that have done business with us from 2005-2008. I want to be able to delete the customers from the worksheet that have done any business in 2008. So that I am left with a list of customers that have not done any business with us in 2008. Can a formula or macro do this for me. I don't just want to delete the rows that contain any dates in 2008, but delete the customer as a whole from the workbook.
TypeDateNumMemoNameItem@Home RealtyInvoice10/20/200522813BIC Clic Stic Black Barrel Red Trim White and Red Imprint Black Ink Medium Point@Home RealtyCSInvoice10/20/200522813Printing an additional color on the merchandise@Home RealtyADDCOLORInvoice10/20/200522813Shipping & Handling@Home RealtyShippingInvoice12/01/2005FC 2058Finance Charges on Overdue Balance@Home RealtyFin ChgInvoice07/18/200730753Silkscreen add web address to shirts@Home RealtySilkscreenInvoice07/18/200730753Silkscreening Set-up fee@Home RealtySet-upTotal @Home Realty1 Source ManufacturingInvoice08/16/2007311575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice08/16/200731157Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice08/16/200731157UPS Ground Shipping & Handling1 Source ManufacturingShippingInvoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/200731875Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice10/25/200731875Shipping & Handling1 Source ManufacturingShippingInvoice07/08/2008343575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice07/08/200834357Shipping & Handling1 Source ManufacturingShippingTotal 1 Source Manufacturing
View 9 Replies
View Related
Mar 30, 2009
I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.
View 9 Replies
View Related
Sep 12, 2009
I currently have a combo box which enables me to create a new customer and save their details to a sheet called ClientTable. I can also press the drop down arrow of this combo box to retrieve an existing customer and their details from the same ClientTable sheet.
My problem is that when I select an existing customer and ammed any of their details - the new information is not being saved. Hence I cannot update an existing enrty once it is created.
how I can adjust the script below to enhance its functionality so customer details can be updated.
Code in the module
Public Sub SaveNewClient()
Dim NewRow As Long, IDNum As Long
If ClientTable Is Nothing Then
Set ClientTable = Sheets("ClientTable").CurrentRegion
End If
NewRow = ClientTable.Rows.Count + 1
If ClientTable.Rows.Count = 1 Then
IDNum = 0
Else
IDNum = NewRow - 2
End If
With Sheets("ClientTable")
View 9 Replies
View Related
Jul 16, 2007
=IF(AND(A1>=1,A11001,A1=10001,A1=25001,A1=1500001),(A1*0.333)+25000)))))
What is missing? (too many brackets at the end?) absolutely I've just mucked around with the formula given so have done something incorrect I think
A1 will contain Sales and I need to work out how much commission they'll get (ie, sales of between 25,000 & 150000 will get 50% plus $2500
View 9 Replies
View Related
Aug 31, 2013
I need to compile a 'sumifs' formula to add sales for the salesmen.
Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.
How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the 'cancel' to sum in my calculations.
Date Salesman Customer Product Status Sale
Jan 8th John Customer1 Apples Good $500
Jan 9th Mark Customer2 Pears Good $200
Jan 8th Kevin Customer3 Oranges Pending $250
Jan 15th Mark Customer2 Pears Cancel $200
Jan 16th Kevin Customer3 Oranges Cancel $250
View 4 Replies
View Related
