I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.
View 4 RepliesIn the first picture the Pivot Table is showing the profit of each city in 2010 and 2012. Where I used Sort & Filter Largest to smallest, in order to display the cities with greatest profit.
Prosent.png
But when I switch the values to `% of` year (Previous). I get the change from 2010 to 2012 in percentage. But my PROBLEM is when I want to Sort & Filter from Largest to Smallest, cause I want to show the cities that have had the greatest percentage change from 2010 to 2012. But the I only get the same cities, and when I look at ex. 'Gjovik' is has had a 125 % in change and should be in the top.
So how am I able to Sort & Filter from Largest to Smallest the cities with greatest change in percentage?
I am trying to find the formula to find the greatest number/date etc in a column, I'm not sure which one I should be using?
For example I have a list of dates in Column B and I want in cell A1 to show the latest date in that column, so if the dates are
21/12/2008
05/02/2009
it would show 05/02/2009 in cell A1.
I have a set series of numbers as follows:
7550-0101
7536-0130
7538-0131
7535-0128
7536-0126
[Code] ....
I would like to disregard the digits before the hyphen (not always four digits) and check only the values of the last set of digits (also not always 4 digits). I would like to search for the greatest suffix used to find the next available number. In my data above the result would be 132.
I have values like this in a column
1
1
2
2
2
3
3
3
3
In this column '3' has the maximun number of repetions of 4. I need to get '4' as the output of my code..
As you can see in the attached sample file, I have different currencies, that all need to be converted to USD.
The exchange rate are displayed on sheet 2.
Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.
Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.
The formula needs to:
Find out what currency the value is in and automatically convert it to USD via the exchange rate values. It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.
I have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:
View 5 Replies View RelatedI'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.
If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for.
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.
View 6 Replies View RelatedHere's a sample of a chart that I want; However, I want the bar chart to be a stack chart as I have multiple "Failure modes" per month that I want to show the number(or count) of failures per month. Then I want the line chart to remain the Complaint Rate for that month.
I just can't seem to figure it out.
This chart pulls from a data set that looks something like this: ....
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies View RelatedDates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C
And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.
In Sheet 2 I have a table of the historical exchange rates like this:
DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089
The way I have this sheet setup is to calculate a 20% fee off the interest earned column "D". Say you earned 6.5% on a beginning balance so the interest earned is in col "D". This works fine for a 20% fee but I need the fee to calculate a 10% fee if the interest in column "C" hit 4% or below and it also needs to be able to calculate a 20% fee if the interested earned is above 4%.
View 5 Replies View RelatedI want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:
1
34
45
26
78
29
7
I can get the result. It shows #num! in cell F2
How to find the greatest common divisor of two numbers? I can not use the built in common divisor function.
View 4 Replies View RelatedFormula that could find the result and return the greatest text value?
I have over a 1000 records broken down by Leaders of which each is roughly 50 records. in those records, answers are either : 4 hours, 8 hours, 24 hours or 48 hours. I need to have a formula return of the 4 choices, which had the greatest value.
I have two columns of data, in another column I have the difference between these two columns, now I want to show the difference as a percentage. Assume cell A is 0.02 and cell B is 0.03 the difference is 0.01 and the percentage of the difference would be 33% or 0.01/0.03, my problem is when cell A is 0.04 and cell B is 0.01 the same formula wouldn’t work in both cases since in the first A<B and the second A>B is there a way that I can take the difference and divide it by the greater of A and B without having to do it manually?
View 6 Replies View RelatedI have managed to get a nice array formula to look up count all the Rs As & Gs in some cells and return whichever there are most of (the cells are linked to/fed from another workbook with cells that have either an R, A or G in - nothing else)
And mainly this is working but on one set of cells it returns nothing as there are 2 As and 2 Gs - what is really annoying is that on a set of cells on a another section of the tab there are 5 A's and 5 G's and that returns an A.
I'm trying to use vlookup function in table 2 referencing table 1 using the approximate parameter. However, an approximate vlookup value is by definition the closest *least* value. Is there a trigger of some sort to locate the closest *greatest* value as well?
For instance, if the lookup_value in table 2 is 2.450 but the referenced table 1 only has 2.445 and 2.475, I will only retrieve the respective value related to 2.445 -- how would I retrieve the other value at 2.475?
Essentially, if a lookup_value rests in between two values of whatever is being referenced, I am trying to average those two closest numbers. Is there a function I've overlooked that may help with this?
I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm
I have used the below code to insert a new row when the value in coulmn A change. I now need to evolve it so that the new row will contain a specific value depending on the changing value:
Before:
Column A Column B
one test
one test
two test
two test
three test
three test
After:
Column A Column B
one test
one test
Coz two............................
How would I add this formula as a worksheet function with VBA. I can't see INT, MOD or Year in VBA. Also want to change C2 to change to activecell column + row 2.
=INT(((C2-1461)- SUM(MOD( DATE(YEAR(C2-MOD(C2,7)+3),1,2)-1461,{1E+99,7})*{1,-1})+5)/7)
I need to search a list in column B of 500 records to find specific words such as"ABC2000000000" The text will always begin with "ABC................." and I would like to extract this whole world from the text string into a new column.
This is not always in the middle or beginning or end.
It could be inbetween other words or numbers or spaces etc - so its not consistent as this data is from a raw file imported into excel.
So I would like to FIND text beginning with"ABC" in column A and then return "ABC123456789000" in Column B. The format will always be "ABC-followed by 12 numbers"
EG
Column A and column B(what I would like to see)
KOREIABC123456789999 4029227
ABC123456789999
RCA ABC000010469597 PBI000010005941
ABC000010469597
8990ABC000010480568
ABC000010480568
Looking for formula to extract values from column A, D through K from raw data table if conditions are met. Desired outcome is in green color.
Sample data attached : Pull.Sample.Data.2008.xlsx‎
Outcome
WestTarget
ProductQ1BBQ1Q2BBQ2Q3BBQ3Q4BBQ4
Product 1348477119150
Product 2578251973823
Product 8763949644583
I have a column of numbers that have certain dates in it. I want to extract the dates and then copy and paste the dates into another column. Shall I use a macro for this or can it be done manually?
Column A1
03.03.2013
1,02043
1,02043
1,01927
1,01988
06.01.2013
1,04778
1,0512
1,04758
1,05099
07.10.2012
The following code sets foundcell.address
View 2 Replies View RelatedI am facing an issue right now to extract time band from a column. Below is the data from where I have to extract Time Band only. I tried different things but its diifcult for me to extract time slot.
New Rate - 19:30 - 19:59 -
Effective From 1st May - 00:00 - 00:59 -
- 07:00 - 07:59 - Branding
Morning - 00:00 - 01:00 -
Night - 21:00 - 22:00 -
LPT - 23:00 - 00:00 -
- 22:00 - 23:00 - PT
- 22:00 - 23:00 - BEFORE EP
The output should be like this
19:30 - 19:59
00:00 - 00:59
07:00 - 07:59
00:00 - 01:00
21:00 - 22:00
23:00 - 00:00
22:00 - 23:00
22:00 - 23:00
I have a substantial schedule that has been completed by a 3rd party who has used the comment function for important data. Can anyone suggest how i can utilise a macro to extract all of the comments into the adjacent column?
The comments are currently against cells in column E and i'd like to extract them to blank cells in a newly inserted column F.