change a formula dynamically

Hi all,

I'm experiencing a tough MS excel function problem. Please give me
some advices.

-------------------------------------
	a	b	c	d
1	3	9	50
2	10	8	40
3		7	30
4		6	20
5		5	10
..		.	.
..		.	.
..		.	.
-------------------------------------

I need to put my results in column D. Currently, D1 should euqal to
"if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)". That means I need to make a
justification that whether C1 is bigger than the summary of B1 to B3.
If it's smaller than the sum, I need to add a constant($A$2) to D1
while reducing B1. Then D2=if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2), and
so on.
But, the problem is that the $A$1=3 is NOT a constant number!!! For
different products, this A1 is different. Perhaps it's 6 sometimes, so
the D1 changes to "if(C1-SUM(B1:B6)>C6,C1-B1,C1-B1+$A$2)". Since
there're hundreds of cases to deal with, certainly I don't want to
change the formulas again and again.
Would you please help me to resolve the trouble? Thank you very much.

zh.y
0
11/15/2004 7:12:50 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
339 Views

Similar Articles

[PageSpeed] 25

I don't follow you. I don't see any reference to cell $A$1 in the formula. Did
you mean $A$2?

If the constant depends on a product number (in which cell?) then you need to
replace the reference to $A$2 with a formula like VLOOKUP that uses the
product number to fetch the correct amount to be added.

But I see another problem: you refer to "reducing B1". Your formula can't do
that. It can only return a value to the cell in column D. In fact a formula in
B1 that refers to B1 will create a circular reference. Maybe you will need to
set up a whole new set of columns for the amended value in column B.

On 15 Nov 2004 11:12:50 -0800, zuohong_yu@hotmail.com (Zuohong) wrote:

>Hi all,
>
>I'm experiencing a tough MS excel function problem. Please give me
>some advices.
>
>-------------------------------------
>	a	b	c	d
>1	3	9	50
>2	10	8	40
>3		7	30
>4		6	20
>5		5	10
>.		.	.
>.		.	.
>.		.	.
>-------------------------------------
>
>I need to put my results in column D. Currently, D1 should euqal to
>"if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)". That means I need to make a
>justification that whether C1 is bigger than the summary of B1 to B3.
>If it's smaller than the sum, I need to add a constant($A$2) to D1
>while reducing B1. Then D2=if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2), and
>so on.
>But, the problem is that the $A$1=3 is NOT a constant number!!! For
>different products, this A1 is different. Perhaps it's 6 sometimes, so
>the D1 changes to "if(C1-SUM(B1:B6)>C6,C1-B1,C1-B1+$A$2)". Since
>there're hundreds of cases to deal with, certainly I don't want to
>change the formulas again and again.
>Would you please help me to resolve the trouble? Thank you very much.
>
>zh.y

0
anonymous (74722)
11/15/2004 8:26:11 PM
It is $A$1, this number refers to a period of time (in weeks)
actually.
To be explicit, I need to change the formulas in column D, according
to the value of $A$1. If $A$1=3,
D1="if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2)"...
When $A$1=4, D1="if(C1-SUM(B1:B4)>C4,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B5)>C5,C2-B2,C2-B2+$A$2)"...


Myrna Larson <anonymous@discussions.microsoft.com> wrote in message news:<ol3ip051ka2caprmlht79lkmpa21c8qadg@4ax.com>...
> I don't follow you. I don't see any reference to cell $A$1 in the formula. Did
> you mean $A$2?
> 
> If the constant depends on a product number (in which cell?) then you need to
> replace the reference to $A$2 with a formula like VLOOKUP that uses the
> product number to fetch the correct amount to be added.
> 
> But I see another problem: you refer to "reducing B1". Your formula can't do
> that. It can only return a value to the cell in column D. In fact a formula in
> B1 that refers to B1 will create a circular reference. Maybe you will need to
> set up a whole new set of columns for the amended value in column B.
> 
> On 15 Nov 2004 11:12:50 -0800, zuohong_yu@hotmail.com (Zuohong) wrote:
> 
> >Hi all,
> >
> >I'm experiencing a tough MS excel function problem. Please give me
> >some advices.
> >
> >-------------------------------------
> >	a	b	c	d
> >1	3	9	50
> >2	10	8	40
> >3		7	30
> >4		6	20
> >5		5	10
> >.		.	.
> >.		.	.
> >.		.	.
> >-------------------------------------
> >
> >I need to put my results in column D. Currently, D1 should euqal to
> >"That means I need to make a
> >justification that whether C1 is bigger than the summary of B1 to B3.
> >If it's smaller than the sum, I need to add a constant($A$2) to D1
> >while reducing B1. Then D2=if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2), and
> >so on.
> >But, the problem is that the $A$1=3 is NOT a constant number!!! For
> >different products, this A1 is different. Perhaps it's 6 sometimes, so
> >the D1 changes to "if(C1-SUM(B1:B6)>C6,C1-B1,C1-B1+$A$2)". Since
> >there're hundreds of cases to deal with, certainly I don't want to
> >change the formulas again and again.
> >Would you please help me to resolve the trouble? Thank you very much.
> >
> >zh.y
0
11/16/2004 8:32:48 AM
To be more simplified, I need to firstly get the value of $A$1, for
example $A$1=6, then I cite this value to be a mark for another cell,
for instance, $B$6. But it's NOT always 6, it's changing according to
different $A$1. However, I tried to adopt something like $B$($A$1),
but it's not possible.


Myrna Larson <anonymous@discussions.microsoft.com> wrote in message news:<ol3ip051ka2caprmlht79lkmpa21c8qadg@4ax.com>...
> I don't follow you. I don't see any reference to cell $A$1 in the formula. Did
> you mean $A$2?
> 
> If the constant depends on a product number (in which cell?) then you need to
> replace the reference to $A$2 with a formula like VLOOKUP that uses the
> product number to fetch the correct amount to be added.
> 
> But I see another problem: you refer to "reducing B1". Your formula can't do
> that. It can only return a value to the cell in column D. In fact a formula in
> B1 that refers to B1 will create a circular reference. Maybe you will need to
> set up a whole new set of columns for the amended value in column B.
> 
> On 15 Nov 2004 11:12:50 -0800, zuohong_yu@hotmail.com (Zuohong) wrote:
> 
> >Hi all,
> >
> >I'm experiencing a tough MS excel function problem. Please give me
> >some advices.
> >
> >-------------------------------------
> >	a	b	c	d
> >1	3	9	50
> >2	10	8	40
> >3		7	30
> >4		6	20
> >5		5	10
> >.		.	.
> >.		.	.
> >.		.	.
> >-------------------------------------
> >
> >I need to put my results in column D. Currently, D1 should euqal to
> >"if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)". That means I need to make a
> >justification that whether C1 is bigger than the summary of B1 to B3.
> >If it's smaller than the sum, I need to add a constant($A$2) to D1
> >while reducing B1. Then D2=if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2), and
> >so on.
> >But, the problem is that the $A$1=3 is NOT a constant number!!! For
> >different products, this A1 is different. Perhaps it's 6 sometimes, so
> >the D1 changes to "if(C1-SUM(B1:B6)>C6,C1-B1,C1-B1+$A$2)". Since
> >there're hundreds of cases to deal with, certainly I don't want to
> >change the formulas again and again.
> >Would you please help me to resolve the trouble? Thank you very much.
> >
> >zh.y
0
11/16/2004 8:42:26 AM
Reply:

Similar Artilces:

How can I change the GUID of a control in an rc file programatically?
Hi all, I have a project that contains a huge amount of rc files that have dialogs in. lots of these dialogs contain custom OCX controls. Some of the OCX Controls have changed their GUIDs, which is out of our control, and now the dialogs wont load (in the resource editor firstly, oviously the code won't run). My immediate thought when I saw this was, "thats ok we can grep the rc files and change the old GUID to the new one". That was before I found that the GUID was no where to be found! I assume the that it is encoded and stored in the DLGINIT structures at the bottom o...

help with formula for last input in column
I would appreciate if someone can help me with this In first column I will type date in some free format 1.01.2012. 3.01.2012 10.01.12. 11.01.11 blank blank blank .... in one cell at the end of table I would like to have the formula that will show me or will copy last input (date, string or cell value) in column or defined range (for instance: A5:A35) is it possible to use formula in one cell for this in Excel 2007 thanks in advance DMag I forgot to write: I solved that on this way, but would like some shorter formula =IF(A49<>0;A49;IF(A48<>0;A48;IF(A47<>0;A47;IF...

change column to row display
I have some data that I paste into a spreadsheet that results in a column of data. Is there a way to paste this column data into a row, or can I convert existing column data in a spreadsheet into a row? Russ Assuming you have less than 256 cells of data (unless you're using XL2007) Try this: Select from the SECOND item down through the bottom of the list. From the Excel Main Menu: <edit><copy> Select the single cell to the right of the FIRST item in the list <edit><paste special>...Check: Transpose....Click [OK] Clear the cells under the first cell. Is that ...

formulas not dragging
In the past couple of months we have had problems with two of our spreadsheets used for payroll. These are spreadsheets that are duplicated and used each week for hours and such. For some reason the formulas won't drag. If you try to drag a formula for totalling columns, the value of the source cell appears instead of the total for each column. We are on a network with two computers using 2000 and one computer using 2002. What is more odd about this is we can go back into prior years in different directories and that particular spreadsheet has the same problem. This happene...

Formula Error #5
Hi, I have a spreadsheet, one sheet per month (ie November, December etc), which has this formula to calculate the daily average of sales (column P, and column U is for account sales, of which there may be not be any for the month), AVERAGE(IF(P10:P70>0,(P10:P70)+(U10:U70))) CTRL+SHIFT+ENTER. This is in cell F72, one row per day, and the >0 part of the formula is there so that future days are not calculated. I have a summary page for this current financial year, and each month contains this formula in cells B6,B8,B10 - B28: ='NOVEMBER 07'!F72 (as per it's month), how...

Change Actual Revenue display in Opportunity
Can anyone tell me how to change the Actual Revenue display name in Opportunity. This field is on the Close Opportunity screen when you Close an Opportunity. I have located the attribute in Opportunity and change the display name but at the Close Opportunity screen it still states Actual Revenue. Thanks. -- dwoo Hi, Close Opportunity screen is based on system entity "Opportunity Close". Display Name of attributes of this entity cannot be changed. You will have to look for some other workaround to implement required functionlity. Kind Regards, Rohit "dwoo" wrot...

Publisher 2003 "2 page document changes to 3 page"
I have couple of files created in Publisher 2003 which worked fine on my computer until the hard drive crashed. After the recovery the text in the file reflows on the 3rd page and automatically. The same files work fine on another computer. The printer for both th computers is same i.e. "HP Laserjet 2840". I also tried Microsoft document image writer as the default printer. I later formatted my computer again and still the same thing happens. The file works fine and prints fine on another computer but in my computer the text flows on the 3rd page inspite of the font size bein...

Change ListView row background color
I have an asp.net listView that I want to change the background color based on a condition. I have started to do something in the ItemDataBound event but cannot find out what method or ??? to use to assign a background color. I have done this in a GridView but this is the first I have tried in ListView. Below is my code if someone can help. Thanks. Protected Sub lvPTODetails_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ListViewItemEventArgs) Handles lvPTODetails.ItemDataBound Dim dataItem As ListViewDataItem = CType(e.Item, ListView...

Help Im a nOOb. cell formulas.
Ok can you walk me through this I am very new to this. I have sheet 1. I have a starting balance on cell H1. Also on that sheet I have a Total on cell D3. Sheet 2 Column E will be my Recieved and Column G will be my paid. I dont know what the sum column is for. I need to have any number entries on Column E sheet 2 added to D3 sheet 1, and subtract from H1 sheet 1. Example: I have starting balance of $1000 in H1. And a Total on D3 of $500. I add $20 to any cell in Column E. We will say E2. Now H1 equals $980, E2 equals $20, and D3 equals $520. I barely know where to start so please be pat...

Add a cell to a fomula already in a cell and keep the formula
Is it possible to add a cell, say A2 which contains a number ($50.00) to cell A1 which contains a formula (=500-45-55-100) and the answer be a new formula (=500-45-55-100-50) so that it just adds the number in A2 to the end of the current formula? Thanks in advance! -- Eric Hi, you will have to do it in a different cell like b2 =A1-A2 "Eric D" wrote: > Is it possible to add a cell, say A2 which contains a number ($50.00) to cell > A1 which contains a formula (=500-45-55-100) and the answer be a new formula > (=500-45-55-100-50) so that it just add...

Page Breaks change with different computer
I have a workbook I created on my system, when I print from my computer everything works perfectly, the page breaks I set work. However, when I email it to my boss and he prints it from his machine everything is all out of whack. I went to Print Preview on his system and I see that all the page breaks are different now then what I set, but when i check the same file on my machine the page breaks are exactly how I want them. I dont want to keep making my boss print 48 pages of a workbook that should print on 14! any ideas??? "Mariano" <Mariano@discussions.microsoft.com>...

History when changing status fields
On selected status fields it should be possible to review the change of the status in a history table. We need to know the date when a project was promoted to the next level. It is interesting to know how much time is spent on different project levels. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I A...

Outlook Express changes HTML code?
I have create a new stationary to use, When I click on it from the COMPOSE NEW button on the main outlook express screen the stationary loads just fine and sends just fine. But if I click on a E-mail I have recieved--- Reply to Sender--- and then in the FORMAT menu-- if I then click on apply stationary and select the SAME STATIONARY file that the compose menu uses. the only thing that occurs is the background color is set.. ALL other HTML formatting is lost, I have looked at the reply message in SOURCE CODE EDIT and outlook express actually changed the HTML code... its just standard ...

Time formulas #3
Hello, I would like assistance with a "simple" time formula. a b 1 9:15:00 9:20:05 The formula I tried to use was "=a1-b1" but no luck. Suggestions would be appreciated. Thanks & Cheers Negative times display as ######## in the 1900 date system. If you want see negative times, you can change to the 1904 date system (Tools/Options/Calculation). For more, see http://cpearson.com/excel/datetime.htm#AddingTimes In article <FE47C4C5-BA28-4C3F-A224-D06929F8BCAA@microsoft.com>, "edver" <edver@discussions.microso...

Help! Formula needed.
Below is an example of data in my excel sheet. A B C D E F Cell 1 ? cell 2 Steve 1 to 10 13/2/04 britain cell 3 Raj 11 to 17 15/2/04 india cell 4 Ricky 18 to 35 14/2/04 Spain Now if i enter a numerical value in A1 (where '?' is), i need a formula which will check the column B and produce a result as desired below Entery number = 9 then print "Steve 1 to 10 13/2/04 britain" Entery number = 12 then print "Raj 11 to 17 15/2/04 india" Ente...

Query works-Export fails "...you and another user are attempting to change the same data..."
Can't figure this out. I am the only user in the database. The file (RealProperty) can be queried using a select query that includes just the fields in the table. I get the above error, however, if I try to export the resulting table/query in any of the following fashions: mdb (in a new db designed to store this one table) ..xls (get the same error) ..csv (get the same error) If it simply try to copy the results, I get the same error. I have checked: there are no dependencies that are missing; there was an export errors table, but the three records that had data issues have been fixed....

Formula Help #19
> The below formula was provided for the question below, can someone tell me how to add into the formula how to not include cells missing information so that it does take those cells into account when calculating the average in cell C1? =SUMIF(A1:A7,"Active",B1:B7)/COUNTIF(A1:A7,"Active") I have a question that I hope someone can help with. > > A B C > 1 ACTIVE 11.88% _______ Average > 2 ACTIVE 3 ACTIVE 12.80% > 4 ACTIVE 5 ACTI...

Tracking Changes Made By Sorting
I am working with a large list and would like to be able to see what changes were made when I sort the data (alphabetical sort). Can this be done without having to go through and check manually? ...

AND formula
Below is my example. I am not able to find any solution for AND fomula. My problem is in cell A, the figure entered is 42. I want this figure to show in Cell D. in other words, how can I create a fomula that determine if cell A is between 31 and 45, mark X (in cell D) A B C D E F -------------|---------|--------|---------|--------|---------- | <15 | 16-30 | 31-45 | 46-60 | >1 -------------|---------|--------|---------|--------|---------- 42 X ...

Formula Problem #14
When I drag and drop a formula it is copying the cells only and not the result, however when I go into the cell and press enter the result is correct. Tools|Options|Calculate If Manual is checked, change it to Automatic. Jerry Tracey BVS wrote: > When I drag and drop a formula it is copying the cells only and not the > result, however when I go into the cell and press enter the result is > correct. Hi goto 'Tools - Options - Calculate' and enable 'Automatic calculation' -- Regards Frank Kabel Frankfurt, Germany "Tracey BVS" <Tracey BVS@discuss...

Formula to delete row when cell contains "Y"
Hi, Column J can contain either "" or "Y". When J contains "Y" I want to delete the contents of columns A:J in that row only. Is this possible using only a formula rather than a macro? ...

Array (CSE) formulas
I am trying to understand array formulas, which I understand are sometimes also called CSE (Ctrl-Shift-Enter) formulas. I found some online tutorials, but am having trouble understanding some of it. This site, gives an example of using array formulas to calculate subtotals and totals. http://office.microsoft.com/en-us/excel/HA010872901033.aspx I cannot see that the array formula solution offers much of an advantage over a more typical solution. Here's a simplified version of the example above: A B C D 1 M N {MxN} MxN 2 1 2 2 2 3 2 3 6 ...

Changing CListCtrl Report View Line Height
Is it possible to change the height of a line in the CListCtrl report view? If so, how? I don't need to make each line a different height (although that would be nice); just changing the global height would be sufficient. Thanks. I don't know how to do it directly, but I know of a "hack" that should work. The ClistCtrl adjusts to height of a row so as to accommodate an image at the head of the row. So, call SetImageList and pass in a CImageList that contains a transparent image of the desired height, and maybe only one pixel wide so that there's not an unnatural spa...

words as data for formula or chart
How can i crate a chart that would use words as data so basically the charts formula would count words in column to output value for example: I have a column where there are words: YES, NO, MAYBE etc... (all in one column) and I like on the chart to show that there are so many words YES, so many words NO, so many words MAYBE etc... in that column.. It could show a bar besically the longest means there is the most of these words ets... Is it possible? Thanks You can create a pivot table from the list, to get a quick summary of the data: Add a heading to the top of the list (e.g....

Help with Federal Income tax formula!
I created some kind of form but doesn't know how to continue....I thin it has something to do with Min and Max :mad -- Outlawdevi ----------------------------------------------------------------------- Outlawdevil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1667 View this thread: http://www.excelforum.com/showthread.php?threadid=31890 This forum will handle an Excel Min/Max issue. Can you provide more specifics about the data and what is or is not working? Dennis "Outlawdevil" wrote: > > I created some kind of form but doesn't...