Variable Number For Use In Formula Calculations

Jun 19, 2008

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 Replies


ADVERTISEMENT

Different Calculations Based On Variable Inputs

Feb 15, 2009

If 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.

View 2 Replies View Related

Variable Commission Rate Calculations?

Mar 29, 2012

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.

View 5 Replies View Related

Drop Whole Number And Leave Decimal Part Of Number For Calculations?

Feb 9, 2013

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

View 1 Replies View Related

Use Variable Row/Column Number In Formula Via VBA

Aug 26, 2006

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

View 7 Replies View Related

Paste Formula In One Row To Variable Number Of Columns

May 9, 2014

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]....

View 4 Replies View Related

Excel Formula For Variable Number Of Columns

Sep 25, 2012

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.

View 2 Replies View Related

Insert Formula Across Variable Number Of Columns With VBA

Jun 19, 2014

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.

View 8 Replies View Related

Conditional Formulas-to Get A Spreadsheet To Automate Calculations Of Unit Costs Based On Variable Packaging Names

Dec 1, 2008

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.

View 9 Replies View Related

Use Two Different Calculations Based On ID Number In Column

Jun 28, 2012

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.

View 9 Replies View Related

Ignore Number Values In Calculations

Oct 9, 2007

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?

View 9 Replies View Related

Display TEXT Only Based On Number Rating And Calculations?

Jun 15, 2014

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.

View 2 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

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 Related

Add Zeros After Formula Calculations?

Sep 17, 2013

I 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.

View 4 Replies View Related

Irr Calculations / Formula For Investment

Dec 17, 2008

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.

View 2 Replies View Related

Calculations Based Off Formula From Another Cell

Jul 8, 2014

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

View 14 Replies View Related

Geometry Formula For Circle Calculations

May 10, 2009

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 Related

Is A Single Formula Better Than Intermediate Calculations?

Feb 2, 2010

I 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.

View 3 Replies View Related

Table Of Calculations Down To A Single Array Formula

Feb 9, 2010

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.

View 9 Replies View Related

Sumproduct Formula Causing Slow Calculations

Aug 23, 2007

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.

View 8 Replies View Related

Change Event For Conditional Formatting Not Firing On Formula Calculations

Sep 25, 2007

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 Related

Set Range Variable Using Variable Row Number

Mar 11, 2008

I 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.

View 3 Replies View Related

How To Get Row Number Of K Variable

Sep 25, 2012

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.

View 4 Replies View Related

Variable For Row Number

Apr 13, 2014

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)

View 10 Replies View Related

Use Last Row Number As Variable?

Jan 20, 2013

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!

View 9 Replies View Related

Use Variable For Row Number

Aug 29, 2006

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

View 6 Replies View Related

Sum A Variable Number Of Cells

May 1, 2009

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.

View 11 Replies View Related

Sum Of Variable Number Of Rows

Aug 7, 2009

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.

View 4 Replies View Related

Display Variable Name Not Number?

Feb 27, 2013

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.

View 3 Replies View Related

Storing Number As Variable?

Jul 10, 2013

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]...

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved