Using A Cell With A Function In It To Do A Calculation
Feb 15, 2008I have this formula: =IF(ROWS(H$12:H12)
View 9 Replies
ADVERTISEMENT
LINEST Function Calculation Using Only VBA
May 7, 2005I can do least squares fitting utilizing LINEST and a worksheet. (See attachment.)
I would like to do the same calculations entirely within VBA.
I can specify the values as arrays within VBA :
Sub linest2VBA()
'
' linest2VBA Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim dXvalSqd(4), dXval(4), dYval(4), dFirstRegStat As Double
Dim i As Integer
Second Question:
Is there a way to get the remaining regression statistics within VBA by referencing only the three arrays: dXvalSqd, dXval, and dYval?
Formula / Function For Commission Calculation?
Jul 26, 2006if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..
$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..
i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.
ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)
Function For Calculation Mode & Status
Mar 10, 2009I am looking for assistance for 2 vbcode functions that will provide current:
1) Calculation Mode - whether automatic, manual or semi automatic; and
2) Calculation Status - whether a recalc is required or not.
These functions need to work regardless of the calculation mode and always need to show the current status.I have tried creating volatile functions, but the problem I face is that if the calculation mode is manual, the function's return value is not updated unless I force a recalc which renders the purpose of the exercise useless.
Date Calculation - Datedif Function
May 11, 2006I am trying to subtract two dates to find out whether an invoice is 6 months past due (regardless of number of days). I use DATEDIF in my formula and it works fine until now. It seems the function takes number of days into account and won't return the desire result when there are 31 days. I want to find out whether the number of months between two date are greater than or equal to 6 months without considering the number of days. I am attaching a sample worksheet for better explanation. As you can see, October is not working right.
View 5 Replies View RelatedFormula/Function For Commission Calculation
Jul 26, 2006I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..
$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..
i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated..
ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)
Sum / IF Function Resulting To Calculation Of Block Of Cells
Jun 3, 2014I have quarters of the year. I'm wanting to find a function wherein when I punch in for example year 2012, the cell would give me the sum of the 4Qs. If if change it to 2013, then it would give me the sum of 4Qs for 2013.
See attached sheet : sum from blocks of cells.xlsx
Custom Worksheet Function And Calculation Dependency
Jan 29, 2010I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies View RelatedSUMPRODUCT Function With Date Calculation Criteria
Jun 11, 2009I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.
One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
Data Sorting, Min/Max, Selective Calculation Function
Jul 30, 2007I'm using Excel 2003 with WinXP SP2.
I'm trying to make a spreadsheet to calculate my GPA for my studies. I can get everything to work fine except I want to be able to calculate an adjusted GPA based on a certain value of classes to drop.
The way it would work ideally is that I would be able to search/sort a data set and if it contained "X" number of entries then the "Y" number of lowest marks would be omitted from the AGPA calculation.
I'm having trouble designing a function/formula or scheme to sort the data range, and identify the lowest "Y" number of entries and recalculate the GPA without using them.
Using Function To Perform A Calculation If Certain Values Are Met, Otherwise Return 0
Jun 10, 2006As an example, cell values as follows.
A1 = 1, 2 or 3, B1 = 2.5
Is it possible to say, if A1= 1 then make C1 equal to B1+4, otherwise return the value of 0?
Combining Two Equations With MOD Function For UPC Check Digit Calculation.
Mar 26, 2009Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,
Clear Cell Content Based On Another Cell Calculation
Dec 21, 2012I have a cell A1 in sheet2 linked cell A1 in sheet1 (simply A1='sheet1'!A1). A1 in sheet1 is a data validation drop down menu.
I want to clear the content of A2 in sheet 2 everytime the content of A1 in sheet2 changes/is updated. That is everytime the value of A1 in sheet1 is changed using the drop down menu.
I tried using a Worksheet_Change event macro (which I do not fully understand) but it won't work with a cell that updates from a calculation. It also doesn't work if triggered from a cell from another worksheet (I tried linking it to cell A1 on sheet1 in this case).
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A2").ClearContents
End Sub
Any simple solution to clear the content of cell A2 in sheet2 when A1 in sheet2 updates?
Automatic Calculation Of Cell Value When One Of The Variables (cell Value) Is Changed?
Oct 31, 2013which will calculate value of a cell when one of the variables has been changed?
To illustrate what I have in mind, an ecxample:
* User can add values to cells using UserForm
- Component name (to Cell "A1")
- Component price (to Cell "B1")
- Component quantity (to Cell "C1")
[Code]....
Calculation Using Last Value In A Cell
Jan 23, 2013I have a list of prices but some cells contain multiple values where the value has changed. I need a formula that will use the last value in a cell.
Example below. As you can see the result column has now value as its using $3.41$3.117 in the calculation (I need it to use $3.117)
Price
Uplift
Calculation
Result
[Code]....
Returning Cell With A YES Or No Calculation?
Nov 11, 2013I am trying to return a cell with a simple yes or no answer and having a little difficulty.
I have entered the following formula which is returning #NAME
=IF(E4<=1.25,ORH4<=0.01,yes)
Calculation needs to be as follows:
Install Cost <1.25 and Profit atleast 0.01
The YES NO needs to be in the Invite? column
Calculation With A Blank Cell?
Feb 17, 2012I am working with a pricing worksheet using the following formula to calculate my selling price. My problem arises when I have either one of the Exchange Rate fields blank or zero. Typically one or the other Exchange Rate would be used, and I have to be sure the one not being used is set to at least 1 or I get an error. Preferably I would like the cell to be ignored if it is blank. I've tried several options but cannot get anything to work.
I'm currently using this formula where the cells have defined names (ie US_EXCHANGE) and F# is my cost.
=F8*(IF(US_EXCHANGE=0,"",US_EXCHANGE*1))*(IF(EURO_EXCHANGE=0,"",EURO_EXCHANGE*1))*MARK_UP*FREIGHT
Not See Value When Calculation Cell Is Blank
Jan 17, 2013In one cell I have the formula =EDATE(D4,3) which looks to the cell to the left (which has a date in it) and in this case with the 3 it gives me a date three months in advance of the date in the cell to the left. This is for sites I visit quarterly. Then for those I visit monthly obviously the 3 is replaced with a 1. so in the cell I get the date when I next need to do my visit.
In the cell to the RIGHT of the cell which has the formula-generated date in it, is a cell in which I manually enter the date on which the next visit took place. In some cases I can't put anything into this cell because I've not been to revisit the place and so I can't put a date in. In the column to the right of this I've got the same formula as referred to above but, because the cell to the left is blank, it's returning a value which makes no sense and really, I'd prefer it that if the cell to the left is blank, the formula doesn't return any value (just leaves a blank cell instead).
So, what would I have to add to the formula above or what would I have to do to get it that if the cell to the left is blank, the formula is still there, but just returns a blank cell???
Auto Calculation In One Cell
May 20, 2008How to use macro to build a calculation in excel using macro, so every time I type a number another cell it has value calculated without click "run" macro.
For example, I want to sum value in Range from F8 to FinalRow automatically, For example, I fill in 3 in D8, 4 in E8 and F8 show value 7; I fill in 4 in D9, 9 in E9 and F9 show value 13.
What is code for this?
Calculation Based On Cell Value
Dec 17, 2007I tried all those search and have no idea what to look for, sorry.
I have attached a single sheet with Footpath Budget for you to see.
In J6, we put in Y or N for Yes or No if there's a nature strip along the footpath.
Now, in cell G49 gives us the total square meters of nature strip.
What I want to do is if I put N in cell J6, cell G49 should be empty or zero because N means there's no nature strip. But if I put Y in J6, then cell G49 can give us total squares from cell C43.
So what formulas can I put in cell G49?
Format A Cell That Causes Sum Calculation To Go Over A Pre-set Number
Jul 31, 2009I'm working with Loan amounts and i need to know how to set a excel spread sheet to conditionally format a cell that causes a sum formula to exceed a preset limit.
Example ...
Perform Calculation Depending On Cell Value
Dec 14, 2012I'm developing a financial spreadsheet.
I've got a cell "Taxable Income" and another cell "Tax on Taxable Income" in sheet2.
When I enter a value in "Taxable Income" I want "Tax on Taxable Income" to automatically calculate based on formulas in sheet1. I don't know how to get "Tax on Taxable Income" to determine which formula to apply from the different tax brackets.
For example:
If taxable income is <$18201, tax = $o
If taxable income is between $18201 and $37000, Tax on Taxable Income =0.19*Taxable Income
If taxable income is between $37001 and $80000, Tax on Taxable Income =3572+0.325*(Taxable Income - $37000)
The idea behind having formulas in a different sheet is so over time when tax rates/brackets change I only have to change the formulas on one sheet.
I know how to use basic AND, IF statements e.t.c but I can't seem to get the syntax right for this to work.
Can A Cell Contain Text And Perform Calculation?
Nov 17, 2009I would like to have a cell in which there is descriptive text and the results of a calculation. For example, in row 2, I want to state the current total number of items in inventory at the top of the sheet in the center of the used cells.
Inventory Total is: SUM(A4:A64,000) as of: Date()
I can't merge cells or manipulate the text in such a way to neatly bring the text and calculation together. And there are actually 2 calculations in the example above. One to SUM the items and one to add the date.
Calculation ONLY If Cell Is > A Specific Number
Nov 14, 2008I want to calculate ONLY if a cell is > the number manually entered in another cell, otherwise, I want the cell to populate with 0.
I need assistance with combining the following into a single calculation:
IF D11 is > D10, then D14 should = sum of D12-D10
If D11 is
Date Calculation Between 2 Or More Dates In The Same Cell
Aug 21, 2009Would it be possible to calculate difference between 2 or more dates in the same Cell?
Dates are in the format shown below (Always seperated by a comma):
Cell A1 = 29Jan2009, 31Jan2009, 14Feb2009, 27Feb2009, 31Mar2009.
In the above case there are only 5 sets of dates, in some cases there are more and in some cases there are less. Between Min 2 dates - Max 7 dates.
Am looking for a result like : 2,14,13,12
Am not well versed with VBA, hence would prefer a formula. Currently am doing Text to Column and working on the same. And that process is time consuming. Would there be a short cut ?
Average Calculation Only When Cell Has Data
Oct 5, 2009Im trying to work out a function that will calculate the average accross a 3 column array. Obviously there is but I need to make any cell's with 'Free' give a value of 0.
I need some kind of IF("Free",1) type thing, to run before the average calculation, but I dont know how to put it together to make it work, I thought about a Loop, but then how do I make sure it only goes to the next cell in the selection?
Enter InputBox Value, After Calculation, Into Cell
Nov 21, 2007I have made a macro to nominate a % rate in an input box , then have a value multiplied by this rate. Now i need the macro to place the rate followed by a '%' symbol in the cell below.
View 9 Replies View RelatedCalculation Based On Name In Cell Text
Jan 23, 2008I'm trying to create an if statement to check to see if a cell has a value in it and return a math function. My problem is that some of the values contain more that just the name searched for. This is a basic example
A B C
10 Williams DXT if(B1="Williams",A1*.8,if(B1="Johnson",A1*.85,1))
20 Williams DPX if(B2="Williams",A2*.8,if(B1="Johnson",A2*.85,1))
30 Johnson AMV if(B3="Williams",A3*.8,if(B1="Johnson",A3*.85,1))
40 Johnson ACS if(B4="Williams",A4*.8,if(B1="Johnson",A4*.85,1))
50 Smith TWN if(B5="Williams",A5*.8,if(B1="Johnson",A5*.85,1))
Presenting The Math Calculation Instead Of Cell References
Jul 24, 2009Assume A1=25 and A2 = 35.
I typed, in cell A3: =A1+A2 which returns: 60.
Is there a way to present, in a single cell, instead of the formula references - something like: =25+35 as TEXT ?
(Any change in A1 and/or A2 will be reflected in the presentation of those two values
I checked some add-ins functions, like "MoreFunc" etc, and INDIRECT - but no results.
How To Get Calculation In One Cell From Sources In A Number Of Different Cells
Jan 17, 2013Please see the attached.
I have put together a sort of hire chart for a charity that hires out scooters/wheelchairs.
All the formulas were done and then they said that they sometimes hire out extra equipment with the chairs as well.
I have created an 'extras' column but I'm having nightmares trying to integrate it into the current formula.
I have just changed the first 3 rows to experiment with.
There is a basic cost of £2 per week or part of a week, that price is regulated from the master page.
Column P calculates the rate from the weeks/days and uses the master page as the source of costing.
I have created a column 'C' for the extras and tried to integrate it into the already complicated formula.
Unfortunately, although it seems to work, it won't work if the cost is lower than £9. At that point, it doesn't matter what I do in column C, it just ignores column C
When it gets to £12 it seems to take notice of column C, very strange.
Wheelchair Hire-Sample New (1)(1) (5).xlsm