Rate Numbers Based On Numeric Range
Apr 8, 2008
In the attached excel file in column C, beginning on row 4, are numbers. These numbers will automatically update on my bloomberg terminal.
(1) I would like to then in column E to have a rating given to those numbers based on the below scale. The way the scale works is that if its 0.6 its a 10. If its anything slightly above 0.6 exactly, its a 9. A 10 is also a 0.5 and any number in between 0.5 and 0.6. The scale is below.
(2) Given that the formula that is being updated is a bloomberg excel formula (in the attached document I pasted it as values), will an excel formula be able to read the updated number in the bloomberg formula? If not, in column D is there a way to have it automatically paste D as values (and update each time column C changes or workbook is saved), and then have column E read off of those numbers.
Scale:
10 = 0.5 to 0.6
9 = 0.6 to 0.7
8 = 0.7 to 0.8
7= 0.8 to 0.9
6 = 0.9 to 1.0
5= 1.0 to 1.1
4= 1.1 to 1.2
3= 1.2 to 1.3
2= 1.3 to 1.4
1= 1.4 to 1.5
0 = anything greater than 1.5
View 5 Replies
ADVERTISEMENT
Mar 31, 2008
I'm trying to add up some values based on a criteria in another column. I have numbers in column A ranging from 0 - 100 and in column B have totals for those numbers. What i am trying to do is Add up the totals in column B based on the following criteria's in Column A.
<7, >7 & <=30, >30 & <=60, >60.
I have a summary page to display the individual results in separate cells.
View 5 Replies
View Related
Jan 15, 2008
I have several cells that are either positive or negative values and the values are expressed in percentages. I'd like to know how many of the cells are over/under certain percentages. For instance, how many of the cells are over negative or positive 2%? I've tried the COUNTIF function using 2%, 4% and so on, but I always get back the same cell count regardless of the criteria I use.
View 7 Replies
View Related
May 21, 2008
how to put together a function that will provide for me 1 of 3 results, ( lower than, higher than or similar to). What I would like to do is have a function to have the results for column B, be either "lower than", "higher than" or "similar to" based on column A. Column A will have a range of %'s from -100% to 100%. What I would like happen is the following results for cloumn B, based on column A. If Column A is:
Between -5% and 5% - Then cloumn B = "similar to"
>5% - Then cloumn B = "higher than"
>-5% - Then cloumn B = "lower than"
View 3 Replies
View Related
Aug 29, 2007
i modify the result but the problem is that when i try it the result alway have a higher number i cannot have a low number...
i want to have the reult from 1 to 1899
here is the
Private Sub CommandButton1_Click()
Randomize
TextBox1.Value = Int(Rnd * 1)
TextBox2.Value = Int(Rnd * 9) + 1
TextBox3.Value = Int(Rnd * 9) + 1
TextBox4.Value = Int(Rnd * 9) + 1
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub TextBox3_Change()
End Sub
View 5 Replies
View Related
Mar 19, 2008
I would like to " Validate Data In A Vertical Column To Not Allow Non Consecutive Numbers Less Than 100"
756415
10
456132
7
456123
12
The above is a valid list. Below would be an invalid list:
756415
10
13
456132
7
7
456123
12
13
This part of a larger scope, but I have a macro that will crash if the data entered is entered by way of the invalid list, it works perfectly with the valid list. I am limited to one column user's will input the data via a Barcode scanner that after it recieves input it enter's a "Hard" return. This is a warehouse pickticket program, user's scan their ticket id's (numbers greater than 100000) and then the number of lines on the ticket (usually not greater than 15)
View 2 Replies
View Related
Apr 19, 2009
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".
Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.
I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.
View 4 Replies
View Related
Nov 19, 2009
I have a spreadsheet and I’m trying to create several different staffing scenarios
Basically what I want is to populate column E with the amount of staffing based on the number of classes in column B. The range that I want to apply is in the green box in the sample. I’m trying to have excel give me the staffing value if the current number in cell B5 is a certain value – so in the example 1 through 4 = .2, 5 through 8 = .4 and 9 through 12 = .6. this site gave me some ideas so I tried V lookup and Index but I seem to be missing something and wonder if it is not combinations of nested functions?
View 3 Replies
View Related
Feb 20, 2013
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?
View 3 Replies
View Related
Feb 27, 2014
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
View 7 Replies
View Related
Mar 14, 2008
I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.
Series
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
and i want to delete 1 to 5 numbers delete to shift cells up.
View 9 Replies
View Related
Oct 15, 2008
I am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this:
1 - 50,000,000.........................(0.17)/1000
50,000,001 – 100,000,000..........(0.13)/1000
100,000,001 – 250,000,000.........(0.10)/1000
250,000,001 – 500,000,000.........(0.08)/1000
500,000,001 – 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000
So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the 250,000,001 - 500,000,000 "range" and automatically apply the corresponding multiplication factor.
View 5 Replies
View Related
Jun 4, 2007
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
View 3 Replies
View Related
Dec 21, 2009
I have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:
View 5 Replies
View Related
May 24, 2007
Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C
And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.
In Sheet 2 I have a table of the historical exchange rates like this:
DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089
View 9 Replies
View Related
Dec 20, 2007
I have downloaded .csv files with my phone call costs. I want to compare phone companies, so I need to calculate the h:mm:ss amounts to $ based on flagfall and rate per 30 seconds. I can open the .csv files in Excel but I do not know anything about Excel.
View 4 Replies
View Related
Feb 5, 2008
I have a situation where I have to determine the required size of a deposit from which constant withdrawals are to be made until the deposit runs out to ZERO. The deposit earns interest at a fized rate, capitalized monthly after each withdrawal had been made.
In my example case, the target month (The actual cell that needs to have a value of ZERO (when the deposit runs out), or the "Range") is found in cell G16. In another situation, it may be found in cell G19 etc, depending on the situation.
It is this changing of the target cell (the one that needs to be ZERO) that has me stumped. I've been thinking of using VLOOKUP (not trying to lead you ;-)) to determine the actual position of the cell that need to have a value of zero but do not know how to build this into the VBA code of Goalseek. This target cell in the Goalseek code should be the cell in column G opposite the figure 1 in column A.
In my example I simply typed in a figure 1 in column A - in the actual spreadsheet the position of this figure is calculated with a formula and its position moves from situation to situation. If the 1 is in cell A19, the target cell, whose value should be ZERO, will then be cell G19..............
View 2 Replies
View Related
Feb 29, 2008
I have a database in this format:
IDProgram NameNIDCvg BeginPlan CodeRate
B00001RT1WILLIAM9999999991/1/03INDEMA5
B00002RT2RAMON9999999997/1/06INDEMA2
B00003RT1MARY99999999910/1/04INDEMA5
The number of rows in the database changes from month to month. I'm trying to write a macro that will pull rates off of a rate table on another sheet in this format:
Plan12345
CCAREA RT1$0.00 $0.00 $0.00 $0.00 $0.00
CCAREA RT2$0.00 $0.00 $0.00 $0.00 $0.00
INDEMA RT1$225.81 $436.89 $415.78 $595.20 $320.80
INDEMA RT2$146.36 $277.76 $264.62 $376.31 $205.49
PACIFA RT1$193.07 $434.40 $415.08 $598.49 $337.85
SH RT2$74.08 $148.16
DELTA RT1$25.85 $51.70 $82.74
DELTAP RT1$39.26 $78.52 $126.76
RLIFE RT1$1.25
I'm trying to create this in a way that the user won't have to manually fill in the formulas each month when the size of the database changes. I can also reformat the rate table if need be, but not the database. I've attached a sample file so you can see what I'm talking about. I'm fairly new to VBA.
View 2 Replies
View Related
Mar 26, 2009
I would like to know how to figure out the vacation accrual rate based on length of employment.
Lets say for years 0-5 the accrual rate is 3 hours per pay period.
for years 6-10 the rate is 5 hours per pay period
for years 11-15 the rate is 7 hours per pay period
for years 16-20 the rate is 9 hours per pay period
and for years after20 the rate is 10 hours per pay period.
I want a formula that will look at say cell C5 (which is the cell that includes length of service) and output the accrual rate. Example. The number 12 is in cell C5. I would like the cell with the formula (D5) to say the number 7 (which is how many hours accrued per pay period based on the table above.
View 3 Replies
View Related
Nov 23, 2009
I have 3 sets of data for two different groups:
Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?
View 6 Replies
View Related
Feb 13, 2014
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies
View Related
Mar 2, 2008
I recorded a macro to perform a graphing operation which will be used in a For-Next Loop which will run eight times. I need to modify the “ range” portion of one of the recorded lines to allow the graphing data to be dependent on variables I have set up as integers. See the applicable part of the recorded code below.
Dim firstrowno As Integer
Dim ltcol As Integer
Dim lastrowno As Integer
Dim rtcol As Integer
‘code which sets the values of the above variables Is Not shown
ActiveChart.SetSourceData Source:= Sheets("PLOTS").Range("AN27:AQ33"), PlotBy _
:=xlColumns
How do I replace/modify the ("AN27:AQ33") in the code above using the variable integers; firstrowno, ltcol, lastrowno and rtcol ?
View 5 Replies
View Related
Nov 15, 2013
I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)
I've attached an example spreadsheet : Variable Fee Schedule.xlsx‎
View 7 Replies
View Related
Sep 27, 2006
Would like to know the formula to sum up values based on a range of numbers.
For example, to sum value in Column B if the range in Column A is within 8100000 to 8150000.
Column A Column B
8110000 110.00
8130000 250.00
9100000 300.00
View 8 Replies
View Related
Sep 19, 2005
I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:
K20) - these are my Numeric Labels.
I then have several other Rows that span the same number of columns as the
Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric
Labels.
I would like to AVERAGE the Numeric Values in each Row and have a Numeric
Label (value) that corresponds to the calculated average returned as the
result.
Example: Sample Data
A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90
A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10
Based on the above data - the Expected Result is 81
Average = 6; Return Numeric Label = 81
The leading zeros shown in A21:K21 is for alignment purposes only.
I would appreciate two formulas:
1) Includes zeros to be averaged
2) Excludes zeros from being averaged
I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),
$A21:$K21,0)) but receive the #N/A error message.
View 14 Replies
View Related
Apr 4, 2008
I have a long list of numbers (1 to around 3000) in Column A and I need to print out only the numbers missing from that list. The numbers are sequential (except the ones missing) and there are no blank or missing cells, only missing numbers.
EXAMPLE:
1
2
4
5
7
.....
So I would only need to print the number 3 and 6. - it can be all in one cell, or in one column, it doesnt matter.
View 2 Replies
View Related
Jul 10, 2014
I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.
writing a formula to evaluate over 360 periods.
View 5 Replies
View Related
Nov 10, 2007
this formula and have tried various nested ifs and I can come up with a formula that works. What I am trying to do it to is detirmine what my multiplier should be based on a range of numbers. Let me try and explain lets say A1 is a number and B1 is a number and I need the Formula for C1.
If B1 is between 0 and 5 multiply A1 by 2 if B1 is between 5 and 10 multiply A1 by 4. and so on and so on. Is there an easy formula for this? I know it might be trivial but im stumped for some reason.
View 9 Replies
View Related
Dec 8, 2011
I have a range of cells (D10:D20) with numbers in them. I need cell A10 to look at D10 and return a specific number based on a set of criteria.
The same thing would go for A11:A20 & D11:D20.
If the number in D10 is 1-2 then A10 needs to display 0
If the number in D10 is 3-8 then A10 needs to display 0.1
If the number in D10 is 9-14 then A10 needs to display 0.2
If the number in D10 is 15-20 then A10 needs to display 0.3
Once I have that formula I know that i will just highlight A10 and drag the formula down through A20.
View 9 Replies
View Related
Jun 6, 2014
In my spreadsheet, each row of data in Column B starts with 3 numerics (e.g. 001) followed by some text. For example:
Cell B2 contains '001 - Text for this cell'
Cell B3 contains '002 - Different Text for this cell',
Cell B4 contains '003 - Different Text for this cell', etc.
I have hundreds of rows which follow this format.
I have had to insert several rows in my spreadsheet in between existing rows which has thrown off the numbering in the first three positions of my Column B cells.
Is there any way to update the data in Column B so the text data in each cell is retained but the first 3 numeric values are updated and again sequential?
View 3 Replies
View Related