I have a variety of products that i sell out to different agents at various levels of commissions. These range from a possible earning for the agents from 12%-30%. Is there a set formula that will take my initial buying price ie £100 and i wish to earn £5 regardless of whether the agent earns 12% or 30%. so an agent on 12% would buy from me at £119.32 and an agent on 30% would buy from me at £150
View 3 Repliesattached file which contains about (150) agents sheets,i need a way which will move me to the desired sheet by pressing its name from the list in the main one , i need this because i shall hide all the sheets.
View 14 Replies View RelatedIs there a way to accurately compute a conversion report that has agents with multiple roles? Right now it is done this way:
Agents are skilled to two skills, sales & service. They will make most of the bookings on sales (few bookings on service). I want a report that will calculate the agents who are on sales/service bookings vs calls while persons on service/sales bookings vs calls.
Right now, because the service/sales agents get way less sales calls, you will find that their overal conversion is way higher than an agent who is a sales/service agent. What this does is drive the overall conversion rate so high that it always favour the agents on service/sales, a sales/service agent is always disadvantaged.
Is there a way in excel where i can more accurately calculate this while maintaining some degree of parity?
See report attached
I have highlighted some of the SE/SA agents that are way high
Note: SE/SA = Service/Sales SA/SE = Sales/Service
I am looking for a formula or VBA code that will allow me to Select a Team leaders name and all the agents reporting to that team leaders would populate. I have the first part of that problem solved but its getting the agents names to generate uniquely that I find problematic.
Below is a step by step example of how i intend it to work
Step 1
I select the Teamleaders name
Shelaine
Once that has populated the Team Names automatically appear
Shelaine
Score
Andrew
20%
John
30%
Mark
40%
Sarah
50%
June
60%
The scores will populate on its own as I will include a Vlookup formula to generate the scores, Its just the Agents names. I am using Excel 2007
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:
Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+
I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.
View 8 Replies View RelatedI have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A
Row2 56
Row3 44
Row4 65
AllData is a named range that refers to the range A2:A65536
AllData_UsedRange refers to A2:A4 by way of this formula.
=OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work...
ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address
Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below.
week39
ABCDEF45939SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046039SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046139SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046239SCHTERR-WIEDEMANNDCCDIST-MUSCHANY0463 SCH Total 14042.2146439SCITERR-SULLIVANDCCDIST-MUSCHANY15587.1546539SCITERR-SULLIVANDCCDIST-MUSCHANY7217.2246639SCITERR-SULLIVANDCCDIST-MUSCHANY5316.6246739SCITERR-SULLIVANDCCDIST-MUSCHANY2720.6946839SCITERR-SULLIVANDCCDIST-MUSCHANY520.446939SCITERR-SULLIVANDCCDIST-MUSCHANY195.7147039SCITERR-SULLIVANDCCDIST-MUSCHANY047139SCITERR-SULLIVANDCCDIST-MUSCHANY0472 SCI Total 31557.7947339SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY4190.7147439SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3219.7647539SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3187.147639SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2209.3747739SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2132.7547839SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2034.56
Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471)
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
Needed a formula in sheet1 in column D where if in sheet2 in column J when the cell value is in the range from 50 to 100 then the cell value in sheet1 column D becomes 0.
The range from 50 to 100 means starting from 50 it can be any one number till 100.
The formula will be pulled down in D column upto the required length.
=SUMPRODUCT(('DVP Trades'!$B$2:$B$1000="LOAN")*('DVP Trades'!$C$2:$C$1000=A31)*('DVP Trades'!$A$2:$A$1000=FUNDS_SS)*('DVP Trades'!$F$2:$F$1000))
Maybe this is impossible, someone let me know.
I am using the above formula. I am trying to use range names where its bolded in place of repeating different cells. However my the result of the formula is N/A.
The range name of "Funds_SS" is a group of 12 funds. What I want to do is take the total of anything that is a Loan in column B, with a given client in column A, if it occurs in any of the funds in range name FUNDS_SS, and the value of the loan is in column F. Does anyone know what I am talking about? Can it not be done? I don't know if I can explain it any better.
I m writing a formula so that if the value in a cell is within a range, it will have a value other than 0.
The formula I'm working on is:
I'm trying to refine my code a bit so I don't have to re-write when my list data changes. How would this code be modified to use a dynamic range name? I have created the range name on the other worksheet using this code in the refers to box of the define name drop down.
View 14 Replies View RelatedIn a cell named item I have a text "unit price".
In a cell named unitprice I have a number 3.45.
In a cell named linetext I have a text "=concatenate(item;unitprice)"
Is it possible to use range.formula to do this using VBA? Is there another way to do this?
I need this type of solution because the formulas in linetext will change based in other cells.
I have a range of cells, D14:IG14, that may or may not have the words "Full Redemption" in each cell. If any of the cells has full redemption, I want "full" to appear in cell II14.
I'd like to do this via formula, but am having trouble with the fact that it's a range I want to look it.
A1: =""
A2: =Text(2,0)
I try =SUMPRODUCT(--(A1:A2)) and CSE Formula =SUM(VALUE(A1:A2)) the result is #VALUE!
What is the formula to sum range A1:A2 which the result is 2
I'm trying to develop an automatical update function for a workbook. I have a workbook, in which I want to updates some formulas. In this regard, I've set up an Add-In. The problem is that when using *. range.formula an error occurs. I understand why this error occurs, but can not mangage to find a solution to solve my problem. The code I'm using follows:
Public Sub DoToAll()
Dim ws As Worksheet
Application. ScreenUpdating = False
For Each ws In Worksheets
ws.Range("B15").Formula = "=IF(D12<>"Age","",IF(AND(YEAR(MID(D9,14,10))=YEAR(TODAY()),MONTH(TODAY())>=7)=TRUE,"Do Nothing",IF(MONTH(TODAY())>=7, DATE(YEAR(TODAY())+1,1,1),IF(MONTH(TODAY())<=6,DATE(YEAR(TODAY()),7,1))))) "
Next
Application.ScreenUpdating = True
End Sub
I have several similar ws.range.formula codes I want to use.
Does anybody know how to insert such formulas via VBA?
Coming from this formula in vba :
VB:
Dim sh As Worksheet
Set sh = Worksheets("Data helper")
Set Rng = sh.Range("C2:C" & sh.Range("C" & sh.Rows.Count).End(xlUp).row)
I want to translate this to a workbook dependent formula, but I'm not sure what I'm doing wrong
VB:
Dim book1 As Workbook
Dim sh As Worksheet
Dim srchRange As range '(added this line in edit : still not working)
'Set some variables:
[Code] ....
I've been trying to use the averageifs formula to determine the aveage FTE needed for 1st, 2nd, 3rd, 4th and years beyond for different client types. Column A displays the client type, Column B the number of years live and Column C is I've come up with the following formula but I can't seem to get it to work right.
=AVERAGEIFS(C2:C16,A2:A16,"C",B2:B16,">=0",B2:B16,"<1")
ClientYearsFTE
C3.612.49
P-0.00
P4.610.16
M0.940.00
M3.280.57
P0.940.00
C-0.06
C3.610.51
C3.450.29
P3.124.18
C4.121.58
C5.371.25
M4.120.63
P7.290.36
M2.611.27
Please refer to attached sheet.
I have number 10000 to 10200 in column D.
I need to use IF Statement and add corresponding number in Column A as follows.
IF D2 = 10000,10006,10012,10018,10024 and so on then A2=20002
IF D2 = 10002,10008,10014,10020,10026 and so on then A2=20003
IF D2 = 10004,10010,10016,10022,10028 and so on then A2=20004
(Basically there is increment of 6)
Once i have formula for A2 then i can copy down the column.
Book1.xlsx‎
how to make make cell D4 dependent on the value in cell D3. So if cell D3 = 10 (it's max), then cell D4 could equal 0-10. I want to use this in conjunction with Solver so that D4 is a variable, but can't be greater than zero until D3 is maxed out. I've already tried different formulas, but I'm not very good at writing them and data validation didn't seem to work either.
View 2 Replies View Relatedi want my formula to look up the highest value from a range (the =max column), then return a name in the leftmost column. What it actually is, is a player of the month for fantasy football. Each week the player gets a score, then each month, a total of four weekly scores. i want to look up who scored the most and return the player name to me. see the attachment.
View 2 Replies View RelatedHOW DO i WRITE A FORMULA FOR 0 TO 5000 = 2% 5001 TO 10000 = 1% and over 10000 = .5%
View 2 Replies View RelatedI am trying to create a time formula for my own work i.e
1 to 6 min = .1 (of an Hour)
6 to 12 min = .2
13 to 18 min = .3
19 to 24 min = .4
25 to 30 min = .5
up to one hour
@ 150 an hour
So if I input in one row lets call it time 3 hours I will like in thenext row rate = 450
1.2 hours =180
6.5 hours = 950 etc
I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!
1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under
blanks or zeros = unknown
I have a 2007 spreadsheet where a person's age is entered into a cell. I need to count how many are between 26-30, 31-35, 36-40, etc.
View 2 Replies View RelatedI'm using Office 2007. I create a Module and with this code
View 4 Replies View Related