I have a single column of numerical values that may repeat many times within the column.
I require a flexible Formula: Use an Input Cell for the specified and changeable Percentage(s) %. Column may be filtered – so only take into account Visible Filtered Cells.
The Formula will calculate and Return the numerical range of values that fall between the specified percentage % (using the Input Cell) e.g.; 70%.
The Formula should Return two numeric values: a Start Value and an End Value – NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the values that appear 70% of the time in the column; therefore, taking into account Repeat / Duplicate values.
The calculated Results: the two numeric values will be returned to separate cells on a new Sheet.
In a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "$50,000 and 10%". I've originally thought about adding the "$", " and ", and "%" separately, but I need the currency value to include commas.
I try to multiply a range with a single value then put the result to another range. But I got the error. range("a1:a10").value = range("c1:c10").value * 5. the error message is the type dismatch. How to do that simple task?
I am looking for an excel formula that will allow me to perform the following logic: if a cell value in (M3:BA3)="D" then multiply C29 by the corresponding value in (M29:BA29), then sum all of the products.
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.
By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.
I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0 If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0 If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0 If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.
Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".
I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have 2 columns - one that has numbers and the second that has text such as A, B, C, D,and E. I need to be able to convert the text into values such as 1,2,3,4,and 5 and then multiply with the first column that has value and display the resultant.
I am trying to build a formula that will give me the 'Final Qty' listed. It needs to be based on 'indentation' level, listed in the 'Belongs To:' column. Exammple: Part 130-0014-02A belongs to 130-0647-00B, which in turns belongs to 139-0086-00D, so mathematically, 4*1*10 (order qty) gives 40.
Can this be done by using some sort of combination of Sumproduct, Index, Match, Sumif formulas? ...
I've tried to replicate my formula (result is in cell J5) via an array formula but keep getting stuck.
Basically there is a 2 dimensional array of weights (range1, limits) each range in range1 and "limits" has a midpoint a constant value (Fixed Amt) is added to each midpoint in range1 then multiplied by another constant value (Inflation Index) The value is then capped by corresponding value in the limits range
this is repeated for all points in the 2 dimensional array (range1,limits) (essentially weighting the results) and the sum is returned.
I've been able to get most of the way with an array formula, but end up collapsing the array when I use the min function.
see the attached sheet. It already has some example....I need the result of the addition in the cells of column F, at the side Say column G, in the coressponding row. e.g for cell F9, I need the result in G9, and so on. For testing, step 1. Select M+R in Col "TOI", enter some value in the pop up. step 2. Again select M+R in "TOI", enter some value in the pop up. the Col F will have some additions (e.g 1+2), for which I need the result in the corresponding next column. i.e col G.
creating functions in excel and would like to hire someone who can assist me in creating the following functions: Column A (For Numerical Values Less Than or = to 100): Column B Value should be (+10)
Example 1: If a numerical value of 90 is placed in Column A then Column B Numerical Auto Value should be 80+10 (190). Example 2: If a numerical value of 35 is placed in column A then Column B Numerical Auto Value should be 35+10 (45) and so on. Another important point is that if the values in column A are copy and pasted Column B should automatically discharge the values visibly as the examples stated above is this possible? If not i'll need to have this done.
Also there are other numerical values that must be added in Column A (basically for every increase in numerical value of 100 there are to be different numerical plus factors in accordance to Column A). Column A (For Numerical Values greater than 100 but less than 200): Column B Value should be (+15).......
it seems that excel is deciding what type of array this is by evaluating the type of the first cell in each column and applying this type to the rest of the array's column. Problem is that i want all values to be brought in, text and numerical.
Dim VArray As Variant Sub DA201() If FileThere(Path & "DA201.xls") Then GetDataTowers Path & "DA201.XLS", "", "DataRange", Sheets _("Towers").Range("CStart"), False, False Else MsgBox ("File DA201.xls not found. Click OK to continue") End If End Sub
Public Sub GetDataTowers(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean) Dim rsCon As Object Dim rsData As Object Dim szConnect As String Dim szSQL As String Dim n As Integer Dim row, m As Integer
I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.
Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.
The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.
In the next post I will include the Amortization schedule.
In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).
I'm absolutely new to Excel and I've been trying to workout a formula to to test two conditions if true and derive the data from a 3rd column. In my attached excel file there's a Grade (A) , Status (B) and Mid Point (C) Tables. I'd like it to be formulated in a sense that forexample:
IF Grade= 1 AND Status= Q4 RETURN =3750*0.25 IF Grade= 1 AND Status= Q3 RETURN =3750*0.5 IF Grade= 1 AND Status= Q3 RETURN =3750*0.75 IF Grade= 1 AND Status= whatever (I mean whatever I input in this cell then it would retrun 3750 ofcourse satisfying the condition grade= 1) RETURN =3750
IF Grade= 2 AND Status= Q4 RETURN =5500*0.25 IF Grade= 2 AND Status= Q3 RETURN =5500*0.5 IF Grade= 2 AND Status= Q2 RETURN =5500*0.75 IF Grade= 2 AND Status= whatever (I mean whatever I input in this cell then it would retrun 5500 ofcourse satisfying the condition grade= 2) RETURN =5500 ................