I have a formula that will be used by others to obtain a certain result by altering a number in the formula. What I would like to do is make that number obvious as the one that can be changed. The formula reads - "=(M48+P48+Q48)*0.08453-(((J48+K48)*$K$4)*0.1)" -what I would like to do is color the 0.08453 in the Formula Bar so it stands out.
View 4 RepliesIf I input any FX pair (Column B) without ???JPY in it, then the formula in Column K produces the correct result. Formula is: eg: If Column B = EURUSD pairs and the like: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*10000.
This formula will produce a +ve or -ve result in points (PIPS) based on the values of E3 and F3. (eg. E3 = 1.2800 & F3 = 1.2750. If C3 = L then the result is -50. Conversely if C3 = S then the result is 50.)
When I input the JPY pairs in Column B, I need the formula to calculate the same way but multiply by 100 (not 10,000). This is because all non JPY cross pairs have 4 decimal places and JPY cross pairs have 2 decimal places. (eg. 1 PIP in EURUSD = .0001, 1 PIP in USDJPY = .01).....
So if Column B = USDJPY the formula would need to be: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*100 . Have tried many combination's of the IF function but am now starting to think this is not quite the right formula for this situation. Attached is a sample of what I am trying to achieve. The formula would need to incorporate any/all variations in currency pairs as listed in the attached sample.
I need a way to calculate commission for my sales reps. The commission is based off a calculated value in H39 of a sheet that I have developed.
They get 7.5% of the value when between $1 - $100,000, example: sales of $50,000 = $3,750 in commission.
If the value in H39 is > $100,000 but $250,000 but $500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000 plus 2% on any amount >$500,000 example: sales of $800,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $7,500 (3% on the next $250,000) plus $6,000 (2% on the sales >$500,000 for a total of $28,500.
How can I drop the whole number part of a number and leaving only the decimal part of the number. Then multiply the decimal part of the number with a number. Then repeat this in a sequence. The object is to convert Lat and Long decimals to Hr. Min. Sec.
eg. 53.535663 .535663*60=32.13978 .13978*60=8 53 32 8
eg. 113.352640 .352640*60=21.1584 .1584*60=9 113 21 9
eg. 113.306579 .306579*60=18.39474 .39474*60=23 113 18 23
I want to create a custom updating formula that finds the last data element on a different sheet and then updates it's own formula. I have to do this because I want to find all of the blank cells and count them up (for missing data purposes). How can I go about putting the last row number in the formula?
This below is the best I can get but it 1004 errors out, I'm assuming because of my "Row" variable. Should I try a concatenate and custom build the formula each time within the macro?
Sub MacroToRun()
Sheets("CleanData").Select
Range("A65536").End(xlUp).Select
Row = Selection.Row
Sheets(" Chart Data").Select
Range("B2").FormulaR1C1 = "=COUNTIF(CleanData!R[-1]C[17]:R[row]C[17],"""")"
End Sub
get this macro to paste the formula across to a variable number of columns? It is falling over at
VB:
Range(Cells(5, .Columns.Count)).Paste
VB:
Sub Clear()
'
With ActiveSheet
[Code]....
I am looking to place a formula which will look like this:
=min("A2:?2"), where ? depends on "number of columns". If number of columns is 4, then it should be D, E or Z. Number of columns is specified as an integer in range("B2").
I do not want to do it in a macro, want to do it in the formula.
I have done this before, but can't quite remember. I have tried:
=min(range(cells(1,2),cells(1,2+B2))), but does not work.
I have two variables - the number of stages, and the number of people.
The stages will start in say C22 through G22 (if there were 5 stages). The number of stages will vary upon a user-inputted number in A1.
The names of people will start inB29, so I'd like to insert the simple formula (=Max(C23:C28)) across cells C29-G29 (again with my example of 5 stages), with relative references to the columns, of course.
Ditto for the next name in B30, etc, so that I get the max value for Person#1 in Stage 1, 2, 3...Person#2 in Stage 1,2,3...
Inserting the formula over the dynamic ranges.
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
I have a list in Excel with several hundred 8 digit ID numbers in column D. Column C contains the total number of hours the student employee worked. Column E needs to calculate using two different equations depending on if the ID number in column D is a manager's. If the ID number is an employee the total hours worked is divided by 8. IF the ID number represents a manager the total hours worked is divided by 4. I've attempted this by using an array for the manager ID list and also using VLookup without success.
In essence I need to loop through column D, check which type of ID number is present, and then enter the correct formula in column E.
I've tried changing the cell format of some number values to text, so the numbers are not read and calculated in to certain formulas, but they're still included in the calculation. I need all values to display in the spreadsheet, but I need exclude a range that would vary.
Is it possible to just change the cell format, so numbers are ignored by a formula?
I am trying to display result of risk assessment matrix in excel. I have three columns PROBABILITY , IMPACT and RISK RATING (probability *impact).
Probability = Very Low(1), Low(2), Medium (3), High (4), Very High (5)
Impact = Low(1), Medium(2), High(3)
Risk Rating = Low (value between 1 -3), Medium (value between 4 - 6), High (value between 7 - 15)
Is there any way that I just use TEXT in the cell (visible to user) and excel does its magic in the background using the numbers that I have for each TEXT? I don't want to display numbers.
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View RelatedI have A1 that contain numbers, B1 that contain number, and i want to multiply those 2 and at the end of result i want to add zeros on front. Let me give you an example:
A1 field has data :00.375B2 has data: 6.49 C1 has the multiply result of A1+B1: 2.43 (But i want when the calculation is done and have the result 2.4 to add 2 zeros on front and become: 002.43 or if the result is for example: 65.20 to add 1 zero on front and become 065.20 , and if the result is 102.20 do not add anything on front.
So in total i need to have the 5 digits of number.
I have a specific problem on irr calculations. In the excel file, I have following data.
date of investment - 1st May 2008
Investment Value - 1000000
Investment close date - 15th Dec 2008
Closure value - 1055000
I want to calculate IRR for the investment for the days the money got invested.
How do I calculate this in excel.
I'm trying to do two things:
1. I'm trying to show the formula of specific cells in the work sheet. So far I've only found out how to show formulas on the entire worksheet. I imagine there has to be some way of me doing this without using macros.
2. Here's the doozy. What I'm trying to do is use the base code for a formula that is stored in one cell (Formula Page: B5:B7) and apply it to other cells (Calc Page: C4:C6). In other words, I want to apply the cell formula from B5:B7 to the cells C4:C6 and use them to calculate the TOTAL COST. I want it so that whenever I manipulate cells B5:B7, they are also appropriately manipulated in C4:C6.
Please see attached for reference. Attachment 33077
See attached photo. I need a formula to calculate the length of line “c1”. I know the diameter of the circle, and the number of segments. In the photo there are 8 segments but this will change.
View 2 Replies View RelatedI come from a (procedural) programming background, where we perform operations step by step. I tend to use intermediate calculation columns, with only 1 or 2 calculations per formula, or, failing that, use a UDF. I find this easier to debug and maintain.
The solutions I see tend to give the result in one formula. This reminds me of the functional progamming languages I used years ago. I'd like your opinions on what is best-practice, or common practice, in relation to constructing formulas, so that I can improve my development.
I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.
The table data is very simple, and can be in just two columns:
1, value
2, value
3, value
4, value
5, value
Where 1 thru 5 are time periods, and values are various numbers.
I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be
=Gammadist(5,x,y,TRUE) (x,y values not important here)
and the formula for the second value would be:
=Gammadist(4,x,y,TRUE)
The only thing that changes is the period number.
So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)
I can get this far:
={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}
But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.
I have the following formula in 220 cells in a workbook:
=( SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$H$1:$H$44995=$A5),'General Journal'!$I$1:$I$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$J$1:$J$44995=$A5),'General Journal'!$K$1:$K$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$L$1:$L$44995=$A5),'General Journal'!$M$1:$M$44995))
Where D3 is a starting date, E3 is a closing date, and A5 is a sorting code. It looks at my general journal and looks for all the entries between the two dates, and then sums up all the entries linked to the sorting code in A5. The sorting code is in column H, and the amount to sum is in column I, and this is repeated 3 times.
You are already probably signering at how long I must have to wait while excel calculates all these formulas (* 220). I have resorted to a macro that turns caluculation to manual when I open this workbook, and back to auto when it closes.
I have used the code for formatting: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm. This works fine when typing in the numbers manualy but if I have a formula (eg:A1=b1+c1) and a1 = 15 nothing happens. Is there an update button or a better way to do this?
View 2 Replies View RelatedI am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
I have find the maximum value from a range by using:
k = Worksheetfunction.max(range("c5"c25")
where k is a variable
Now I want syntax to get the row number of k variable.
I write a function like below
Function ABC()
dim row as long
row=???(how should I input?)
...
...
end Function.
the formula in "A3" cell is ABC()
When the ABC() run, How to let the function ABC know the row? (answer is 3)
In my spreadsheet I have around 30 macros... and the majority of these macros make reference to the last row of a worksheet (in my case the last row happens to be 4867). There are some 50 or so references to this last row (albeit in different columns such as '$A2:$A4867' or '$CQ2:$CQ$4867' but in any case, this number '4867' occurs rather a lot.
The last row number only ever changes when I add more rows to the spreadsheet every 3 months or so, and when I do this, I just simply go through each macro carefully and Ctrl+F and replace all mentions of '4867' with whatever the new last row number is. I want to make this a little more efficient by using a name such as 'MyLastRow' and use that in all the macros and formulas so whenever I add new rows to the sheet, I just need to type the new row number in one place, and all the macros will adjust themselves accordingly.
I have tried defining a name for the last row (calling it 'MyLastRow' and giving it a value of '4867' but I cant make it work in formulas even simple ones such as =SUM($A$2:$A"MyLastRow"). I think Im on the right track, but am struggling to find resources and good examples to guide me....
My spreadsheet is extremely large (621 columns x 4867 rows), so I am looking for a very efficient way to do this using non-volatile functions and not using named ranges as I understand these can slow down performance as they use the volatile OFFSET function... and besides, with 621 columns, I do not wish to have 621 defined names!
how can i assign the refrence to the rowa using variable. I am getting an error due to line Rows("j:k").Select in the below mentioned code
For j = 2 To 2000 Step 2
k = j + 2
Rows("j:k").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Next j
I would like to have a cell in A2 sum up the values in cells A1, B1, C1 where the number of cells it sums is a variable in another cell that may change. I would like the same to apply to cell B2, C2, D2, etc.
This may make more sense if you look at the attachment.
I reckon this should be easier than I'm finding it, but I just can't seem to find the right combination of functions to solve this. In column A I will have an entry every 5 or 6 or 7 rows with blanks rows between. In Column B starting at every row corresponding with an entry in Column A there will be a short column of figures could be 1 2 3 or four or more of them. (the're will always be a blank between the last number in a group in B before the next entry in A). In column C starting at every Row corresponding to an entry in column A I want the sum of the group of figures in Column B.
But here's the rub.... The most important thing is that I can use the same formula all the way down column C. So click the one formula at the top and drag it all the way down 1000 rows.
I am creating a very simple budget report for a tournament I am hosting. I want to specify whether payment was from Check or Paypal and want each one to resemble a value. This is because PayPal takes a service fee and when we get a 50$ payment it is actually 48.25$. So basically I have 4 columns for the revenue side of the budget report. The school that is registering, Payment type, Number of participants being sent, and total payment received from the school.
My hope is I can have Payment Type*# of participants to equal total payment, but not have a number being displayed for payment type. I would prefer to have payment type say PayPal or Check or at least P and C instead of 48.25 and 50 respectfully. I found a way to insert a name for a value, but when i type =PayPal the value of 48.25 comes up and i cant find a way to format the cells to display PayPal and have it just represent 48.25.
My code needs to find the last date listed in column A, which it already can do. And calculate how many days have passed between today and that date it finds in the column. Which I think my code can already do.
How do I store the number for later use?
Meaning, the next part of this code is going to send a request to a website to retrieve however many days of data that number is to be above ^. So it might be 2 days, 12 days 7 days, who knows...but the http request part will come after.
Code:
Sub date()
Dim lngLastRow As Long, lngRow As Long
Dim strColumn As String
strColumn = "A"
With ActiveSheet
lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
For lngRow = 2 To lngLastRow
[Code]...