I 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.
Column A gives the request date of the order, based on the TAT in column B the projected dispatch date gets calculated.
Once the order is dispatched and the date gets updated in column Z, the redemption status in column V gets updated as "Dispatched within TAT" or "Dispatched out of TAT" based on whether they have met the TAT or not. If the dispatch date is left blank then it shows "Yet to be dispatched".
My problem is that the column D does not stop calculation even after the order is dispatched. And therefore because of the conditional formatting turns red the minute the number is more than 0. So it is misleading.
I want to use a formula to detect which name is appearing most in a selected range of cells. The Mode formula gives this result but for numbers. I was thinking of the probability of having to combine the LEN function with the MODE function.
How would i find the mode of a range of numbers that changes?
For example, I want a simple out put of the Mode for the numbers after Aand D, and E and so on, without having to do it manually. A function perhaps?
I have attached spreadsheet. The 2 left columns are the raw data and the right columns are what i want the outcome (modes) to be. Is there a way I can do this without having to go through this manunally for every one?
I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.
I’m using excel 2003 and I need help with the MODE function. I have a spreadsheet with 142 work types’ rows of work with each one showing the last 13 months of work received. I have set up a mode function for each work type, but for the ones when excel cannot work out the mode it puts n/a in and I have to do them manually, by looking at the last 13 months data and basically guessing a mode figure – which is a pain (e.g. if there was 13 different numbers from say 120 – 135 excel would give n/a but I would put the mode at around 128. As this spreadsheet gets updated monthly is there a way I can trick excel into giving a mode figure, or a figure close to what the mode should be. I have attached a sample of the spreadsheet.
I am trying to use the MODE function over multiple sheets within a work book, however when using =MODE( 'First Page:Last Page'!K5). the formula returns "#REF!". this should be returning 7, 8 or 9. I have already checked to see if the range is wrong, but when I changed the function to SUM, AVERAGE, etc they work fine.... however due to the need for non-excel users to easily add extra pages, adding each page individually would be a pain (and would mean I would need to train the staff in Excel)
i've tried searching for "Mode Function in Text" , "Mode Function by Text" but to no avail. Can someone please guide me in how to do a Mode by Text? =MODE(number1,number2,...) But if i have a whole chunk of data in Strings in cells Range A1:C50
1) How can i get the mode of that Range ? 2) is it possible to get the statistic of those for e.g.
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
I 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?
if 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..
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)
I 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.
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..
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)
I 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
I 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:
I 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.
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.
I want to find out the most frequently occurring value for the below example. Based on the example below I want to know Column A = Sales, Column B = s682, which value is the most frequently occurred.
Is there a good/easy formula to use when trying to find the mean, median and mode for a column? If so, can someone post it? I'd like to make it as easy as possible (obviously!).
I have done the two obvious checks regarding my missing Status Bar, both in View and Options and still the Status Bar refuses to show itself. I have shut down Excel with the options unticked hoping that on restarting and ticking them the bar will re-emerge but still no joy and also done vice-versa. how to get the Status Bar to reappear?
Does anyone know of a site or some code that can make changes to add some items to the status bar calculations?
Such as right now you have Sum, Count, Count Nums, Max, Min... just to name a few.
I'd like to be able to add some other ones down there if it can be done. I'd like to count #N/A's or possibly sum only positive numbers? I can do it with a quick keyboard shortcut and message box with a macro, but I'd like to just be able to highlight a range and have it show up like sum or the others do.
