I have a question regarding a vba formula in excel. I am trying to take a whole row ( range) of data and multiply each cell within that range by -1 if a particular cell is an Asset or Liability.
Function AFTER_REFRESH(Argument As String)
AFTER_REFRESH = True
If Range("G5:H5").Item(1, 55) = "LEQ" Then
Range("G5:H5").Value = (-1) * Range("BM5:BN5")
MsgBox "Yo"
Else
End If
End Function
I want excel to multiple the values in a specific range.
For example:
I will give 2 dates: MAR 2006 and MAR 2007. Excel should multiple the value 0,76 and 0,71. It's simples because is 2 months. But I have a lot of months (2006 until 2009).
So I would give MAR 2006 and MAR 2009 and excel should multiple the values from MAR 2006, 2007, 2008 and 2009. All the values in the range, but only for March.
The month will never change between the first and the last date.
I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.
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.
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 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 have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:
The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level
0 = Top level 1 = Level Below 2 = Level below that etc, etc
If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity
THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number if the quantity fields are blank, then always multipy below by 1
This needs to continue on down the chain of levels
So basically you end up with the correct quantites of each item at each BOM level.
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? ...
After ripping a .pdf report into Excel I am left with a variety of items in Column A, numbers as text, text, and blanks. I manually selected entire column and paste special multiply. Seems to have convereted blanks to zero, converted numbers stores as text to numbers, and ignored any text. Problem is writing macro to repeat the steps as will use the tool every period. I tried looping through the range
For Each C In myRange C.Value = C.Value * 1 Next C
But this returns type mismatch error. So why did it work manually if won't work through the macro?
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).
Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [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.
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 ................
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 would like to multiply a serie in 'series values' (before presenting on a chart) with a factor (in a formula like ='[1]name'!$V$2:$V$148)
in other words I would like to do something like =('[1]name'!$V$2:$V$148)*50
but I don't seem to succeed with the correct syntax.
In Excel searching for "multiply series values" under "edit series" there is to read:
"Use this option to include additional data series on the chart or to modify the name and values of existing data series without affecting the data on the worksheet."
So what I want to do seems to be possible.
Excel 2007 version 12.0.6611.1000
I know I can create a temporary column with the multiplied values but THIS IS NOT the way I want to solve the problem.
Is anyone able to see where i went wrong, i am tring to make a function sumproduct using dynamic ranges. i can do the same thing using arrays which is simple(below), but not using ranges, below. the range is dependent on a the number of selected choices, hence the ubound.
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
I have .csv file from which the data is importing to master schedule. i have a column in .csv file which is spitted into multiple columns. and my need is i have to compare this each individual cell value with the range of header values in master schedule and if match found i have to place that cell in the row.
I am adding 2 attachments one is .csv and other is master schedule.
I couldn't able to find where i have to attach my files.
I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
I am having trouble creating a formaula to calculate value based on the sum of a cell. For example if the sum of cell A1 is <= 149 I need to multiply the sum by 1.0 if the sum of cell A1 is 150-199 I need to multiply the sum by 1.50, if the sum of cell A1 is 200-249 I need to multiply the sum by 1.75, and if the sum of A1 is >= to 250 I need to multiply the sum by 2.00
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?
/code Dim cell As Range For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp)) If cell.Value = "xx" Then _ cell.Value = "01" Next cell /code
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A B C D Dept E Account T F F2 G Debit Credit Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A B C D Dept E Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
