#### Formula for Dynamic Range?

```I am inexperienced and need help:

Need to create formula for:

A	B	C	D	E	F
Row1	1	2	3	4	5	6
Row2	2	4	6	8	10	12

Row3
Needs to include formula
that results in range changes in row2 based on the value cell in row1.

I do not have an idea of what to use.
Thanks

```
 0
VCUE (2)
10/6/2007 3:37:02 AM
excel.newusers 15348 articles. 2 followers.

3 Replies
443 Views

Similar Articles

[PageSpeed] 2

```> Row3
> Needs to include formula that results in
> range changes in row2 based on the value cell in row1.

Can you provide some sample values of what should appear in A3, B3, ... F3
given the values in A1:F1 and in A2:F2, and explain the logic behind how
these values are computed?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

```
 0
demechanik (4694)
10/6/2007 4:02:33 AM
```I am using logical functions:
Row1 is: "Month"# formated cells
Row2 are incremental values (not necessarily B2*2=4)
In Row3, For example =if(B5=>C1,SUM(\$A\$2:C2),SUM of a range that moves one
column to the right starting at A3 and that will expand until the range in
row3 will COUNT the number of cells that is equivalent to an arbitrary value
in B5

"Max" wrote:

> > Row3
> > Needs to include formula that results in
> > range changes in row2 based on the value cell in row1.
>
> Can you provide some sample values of what should appear in A3, B3, ... F3
> given the values in A1:F1 and in A2:F2, and explain the logic behind how
> these values are computed?
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>
```
 0
VCUE (2)
10/6/2007 5:11:00 AM
```Hi

I'm not sure that I understand what you are trying to achieve.
Perhaps something like
=SUM(INDIRECT("A2:"&CHAR(64+B5)&"2"))

--
Regards
Roger Govier

"VCUE" <VCUE@discussions.microsoft.com> wrote in message
>I am using logical functions:
> Row1 is: "Month"# formated cells
> Row2 are incremental values (not necessarily B2*2=4)
> In Row3, For example =if(B5=>C1,SUM(\$A\$2:C2),SUM of a range that moves one
> column to the right starting at A3 and that will expand until the range in
> row3 will COUNT the number of cells that is equivalent to an arbitrary
> value
> in B5
>
>
> "Max" wrote:
>
>> > Row3
>> > Needs to include formula that results in
>> > range changes in row2 based on the value cell in row1.
>>
>> Can you provide some sample values of what should appear in A3, B3, ...
>> F3
>> given the values in A1:F1 and in A2:F2, and explain the logic behind how
>> these values are computed?
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>>
>>
>>

```
 0
Roger
10/6/2007 6:56:16 AM

Similar Artilces:

Formula calculating fulltime/parttime vs employees.
I have a spreadsheet listing employees jobs in one column. Another column lists if they are full time or part time. There are several employees with the same job but work different times. I need a formula to calculate how many people with that title work full time and how many people with the same job work part time. A pivot table will do a very nice job for you. They are very powerful once you get to know them. Take a look at Chip Pearson's site for a tutorial on them: http://www.cpearson.com/excel/pivots.htm -- Regards, Fred "VP" <VP@discussions.microsoft.com&...

Excel Formula for determining azimuth relative to true north between two sets of geographic coordinates?
Using the geographic coordinates for each, I've "translated" the FCC procedure for determining the distance between two transmitters ( per FCC 73.208) into Excel formulas. Excel gives identical distance results to the "fill-in" form provided in www.fcc.gov/mb/audio/bickel/distance.html . But there is no comparable formula in the FCC R&R for determining the bearing (azimuth) between the stations relative to true north, although the FCC's fill-in form does provide azimuth. My problem with using the fill-in form is that I have hundreds of computations to make and...

HOW DO I ENTER DATA WHEN I SEEM TO BE STUCK IN A SUMMATION FORMULA
WHEN I OPEN MY SIMPLE SPREAD SHEET AND ATTEMPT TO FILL ONE CELL I AM STUCK THERE AND AS I TRY TO MOVE AROUND I GET THE BLUE SHADING AS IF I WERE DOING A SUMMATION. I CAN'T GET RID OF THE SHADING OR GET IT TO ENTER DATA? -- K J PETERSON hi typing with all caps is considered to be shouting and therefore impolite. press the F8 key once and see what happens. Regards FSt1 "kenneth j peterson" wrote: > WHEN I OPEN MY SIMPLE SPREAD SHEET AND ATTEMPT TO FILL ONE CELL I AM STUCK > THERE AND AS I TRY TO MOVE AROUND I GET THE BLUE SHADING AS IF I WERE DOING A > SUMMATION....

If Count is not in a certain range, delete Rows
I have a spreadsheet that is 59752 x 2, with the one column Contact ID A and the other column Contact Link ID. It is like an instant messaging system. It looks something like this: 1 2 1 3 1 15 2 1 2 15 I want to count by different values in column A, and any count that is not within one of several ranges, those rows get deleted. So if the range is 3-5 contacts, then I want to keep the set of 1s, but delete the set of 2s. My ranges are 4-5 contacts, 30-41 contacts, and 100+ contacts. I am also brand new to macros, but I have experience with some other coding ...

Date Range on Report in Money 2003
I have a favorite report in Money 2003 which lists scheduled payments within a range of dates. My file was upgraded from Money 2000. I used to have the problem of the report omitting many of the scheduled transactions within the date range. That was fixed by running the Salvage program. Now the problem is the other way around: the report shows transactions BEYOND the date range, as well as those within. Is there a fix for that? I still have the Salvage program. :-) Thanks, Elliott ...

Formulas within Cell References
Hello All, I need to use a number that was calculated from a formula in a cel reference. Here is the situation: I have a large spreadsheet tha changes periodically. I need to count the number of items in tha spreadsheet(i will use COUNTA - already works) and then take tha number and use it as a row number in a cell reference like this: In cell H1: =COUNTA(A2:A9999) Then take that number in cell H1 and insert that into say \$J\$XXXX wher XXXX represents the number in cell H1. I'm looking to increase the calculation speed of the workbook by onl calculating cells that I need to. Thanks f...

Restoring Dynamics Database
Hello gurus, We are are currently working on a test server where all user security has been setup. We will be moving to the production environment and will rather not have to re-do all the user security. I know company security information and common system files are stored in the Dynamics database. My questions are these: can we simply restore the Dynamics database to production? Any risks to doing this? Besides user security, what other information is updated in Dynamics database? My main concern is, as part of testing, we have processed several transactions, closed existing...

Display ranges from one worksheet to another
I am running excel 2003. I have been given the task of creating a workbook where on one main sheet I want to display named ranges from up to 4 other worksheets based on the user selection. The ranges are work weeks and we want to be able to display 8 weeks at a time for resource planning. Can someone point me in a direction where I can find a way to do this? Thank you. Check Ron's site under the Copy/Paste section. http://www.rondebruin.nl/tips.htm "Opal" <tmwelton@hotmail.com> wrote in message news:eb392038-e6ab-46b0-a3d6-eb0df5c7a0fd@h2g2000vbd...

Custom Fields/Formulas Receiving an error "NA" in Project Center
Hello All, Was wondering if I could get some help, we are running project server 2007 latest edition with all patches. And are running a custom formula. "IIf([Milestone]=Yes,"Milestone",IIf([PPC_Result]>=1,"100.00%",IIf([Baseline Start]=ProjDateValue("NA") Or [Baseline Finish]=ProjDateValue("NA") Or [Baseline Duration]=ProjDateValue("NA"),"No Baseline",IIf([Baseline Start]>Now(),"Baseline start in future",Format(([PPC_NUM]/[Baseline Duration]),"percent")))))" The formula runs off a ...

Formula to calculate interest only on a short term loan?
I am not familiar with Excel 2007 and need to calculate a bridge loan MONTHLY INTEREST ONLY scenario, with a fixed %rate, for a range of 1 -6 months. Not sure if if interest accrues daily or monthly. Assuming you have an annual interest rate, a month's interest is: =Principal*IntRate/12 This may be good enough for your purposes. Most financial institutions would calculate the interest based on the number of days in the month, so something like: =Principal*IntRate/365*day(eomonth(a1,0)) Regards, Fred. "Excel2007Help" <Excel2007Help@discussions.microsoft.c...

Dynamic "Numbered List"
How Can I update my numbered list (S.No) after Filtering the list? Like: 1. A 2. B 3. C 4. D After filter..... 1. A 2. B 3. D [C Filtered] Any Idea to get the result. Regards, Murtaza hi, Murtaza ! > ... update my numbered list (S.No) after Filtering the list? > Like: > 1. A > 2. B > 3. C > 4. D > After filter..... > 1. A > 2. B > 3. D [C Filtered] assuming [B2] as the first cell [in filtered-column]... try with subtotal-offset approach [by Laurent Longre] and copy-down... a) array-entered: {ctrl}+...

Excel displays formulae, not results
I have inherited a spread sheet that has a number of formulae in it. However when I type in a new formula, it displays it as the formula, not as the result. This is not applicable to all cells, just some of them. I am now reduced to copying a formula that displays the result, then editing it to give the formula that I actually want. Obviously there is some setting in the individual cells that governs how it displays as well as the general setting. Hi Roger, This happens when a cell is formatted as text the moment -- Kind Regards, Niek Otten Microsoft MVP - Excel you enter the formula. C...

Using Office2000 Sp3 I use Excel a lot for historical things including eg; areas of land expressed in ACRES ROODS PERCHES which is not added in tens (similar to �/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a formula which when applied to entries across 3 columns gives a decimal answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in D1, Roods E1, perches F1. Once this is decimalised I can work out ratios etc. However, I occasionally get tabbed text files or excel files from people who have entered lots of raw data as eg ARP where it would be ...

formula to project revenue
Hello, is there a formula I can use that will project revenue in real time based on total sales at any given time. Basically, take my sales to date, consider how many days into a calendar year we are at presently and make a prediction to where the sales will be at the end of the calendar year based on sales to date. So, say I am 50 days into the calendar year and my sales are 500,000. Presently, I am averaging 10,000 per day and at this rate my sales for the year will be 3,650,000. Is there a formula that will take the revenue from cell1 and provide the annual projected reven...

formula #60
Dear all, When I enter "=A2" for example into a sheet it stayes as =A2 instead of bringing the data. Can anyone explain this please? XP Pro SP2 Excel 2003 John North Yorkshire UK My guess is that the cell is formatted as text. Go to Format, Cells, Number. Under Category click General and then click OK. Then retype =A2. Hopefully this will fix it. "John Proud" wrote: > Dear all, > > When I enter "=A2" for example into a sheet it stayes as =A2 instead of > bringing the data. > > Can anyone explain this please? > > XP Pro SP2...

Counting characters in a range of cells
Is there a way to count to number of times a given charachter occurs in a range of cells? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ David, If it's a worksheet function you want, try =SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"e",""))) which is an array formula, so enter with Ctrl-Shift-Enter. If you want to do it in VBA, use Debug.Print Application.Evaluate("SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,""e"",""...

cell outside range
Intermittently, a cell erroneously appears outside the range of cells in an active worksheet. This cell contains duplicate data from one of the other cells. I am not able to replicate the sequence of events that causes this cell outside the range of cells to magically appear. I wonder if anyone else has seen this problem. Thanks, Mrs. T Are there any macros in any open workbook? "Mrs. T." wrote: > Intermittently, a cell erroneously appears outside the range of cells in an > active worksheet. This cell contains duplicate data from one of the other > cells. I am ...

Different formatting in a cell with a concatenated formula? #2
Yes, if a1 = blue, and b1 = red, and the formula in cell c1 is =a1&b1, want the result to look like this: bluere -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 Formulas don't support this character by character formatting. If you can convert the results of the formula to values, you can do it though--but that destroys the formula. andy2812 wrote: > > Yes, if a1 = blue, and...

Add rows to a formula when copying
I have the following formula in cell C3 =IF((Calculations!C9-Calculations!C15)=0," n/a",Calculations!C3/(Calculations!C9-Calculations!C15)) I wish to copy this to cell C16 but wish to add more than the 13 row (ie 16 - 3) to each reference in the worksheet Calculations. I need i to add 27 rows. The end result formula I need in cell C16 is =IF((Calculations!C36-Calculations!C42)=0," n/a",Calculations!C30/(Calculations!C27-Calculations!C42)) Is there any easy way to do this? Happy to do it using a macro i necessary. To sum up, I would like to copy a formula and...

Can't put range on chart
Hello, I have a spreadsheet with 50 tabs. Each tab has a 6 month audit score that looks like this: MAR APR MAY JUN JUL AUG 100% 100% 100% 100% 100% 100% There is a bar graph linked to these cells on each tab. What I am trying to do is group the tabs together, then delete the MAR score, shift all cells to the left, then add a SEP score. The problem is... when I try to do that, the graph changes to only show APR MAY JUN JUL & AUG. I can't get it to automatically add SEP. I could do it individually but that would be very time consuming to do it for 50 ...

Fixed Asset Tax Reporting
MS Dynamics does not support tax reporting. Most other fixed asset systems, like FAS100 by Sage, support this functionality. Need help in supporting the following forms: 4626, 4797, 4562. The 4562 can be recreated by running various reports. The other 2 forms cannot; too much logic involved. Also, this logic can change from year to year. Without this, fixed assets in MS Dynamics is not usable by relatively complex companies. -- Bennett Cikoch ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this ...

dynamic charts
Hi: I have a series of monthly reports within excel. Each of those reports has a raw data tab. The raw data goes from left to right and each column represents an additional month. The rows represent different metrics that are tracked over time. How can I create my charts so that when I add an additional months worth of data (ex: adding a column to the raw data table) the charts dynamically update with a rolling 13 month trend? I have seen other examples where the raw data is setup differently and can't format my data that way. Specifically, people had the months in each row and the ...

Formula for cycle time
Hi, I need a formula to determine cycle time... actually to calculate the number of days from the date in one cell to the date in the other and put it in a third cell... thank you! JBell XL stores times as integer offsets from a base date, so you can use regular math functions: =A2 - A1 In article <1bf6e01c45232\$5564ac40\$a601280a@phx.gbl>, "JBell" <anonymous@discussions.microsoft.com> wrote: > Hi, > I need a formula to determine cycle time... actually to > calculate the number of days from the date in one cell to > the date in the other and p...

averages/if formulas-need help ASAP!!
I have two columns: one has initials of person completing the task, next to it is the percentage of work that is equal to. At the bottom, I am hoping to have two tables depicting the average % of work and total % of work for each person. I've tried =average(e3:e23 (if(d3:23="dj")) Basically I have no idea how to create a correspondence between the name and % of work...HELP! I'm so confused I can't even really explain my problem :) >I've tried =average(e3:e23 (if(d3:23="dj")) That's pretty close! Try it like this... Array ente...

Dynamic Named Ranges
I have a chart which displays 30 days worth of data at a time, linked to another workbook. I am charting a dynamic named range in the source file however my chart data disappears every time source workbook is closed (not cool!) need a way around this without using macros.. Apprceiate any help ...