On Mon, Mar 4, 2013 at 1:39 PM, Tammy Ma <metal_lical...@live.com> wrote: > > HI, > > Assume I have the data frame generated from R as the following: > > Product Price market_share > A 100 10% > B 110 9% > C 120 20% > D 90 61% > > What I want to do is to have this kind of excel user interface by changing > price of certain products, to get the impact of market share generated from R: > if I change A price from 100 to 90, and change B price from 110 to 100, based > on the calculation in R, I want to get the result from excel, for example, > > > Product Price market_share > A 90 20% > B 100 12% > C 120 10% > D 90 58% > > > I want to have the excel interface which make me be able to change the value > in excel cell, then based on the calculation in R, then exported market share > impact in excel. > > DO you have recommendation for such user interface or sth which make me > achieve this task?
This is just not how you *think* in R. Formulas in Excel are all hidden, and stuff happens automatically, and if columns are all dependent on one another then you are redundantly storing information. If I was doing a table like this I would only store the original information, and then write some functions to produce the spreadsheet with all the derived columns. I would only change the original information, and then re-run the function that generates the report. Now, I really don't understand in your example how changing the price of A from 100 to 90 results in a change of market_share from 10% to 20%, so I guess there must be some complex formula going on. I'll simplify.... Suppose your data is Product, Price, and Unit Sales: > d = > data.frame(Product=c("A","B","C","D"),Price=c(100,110,120,90),Sales=c(3,5,3,7)) > d Product Price Sales 1 A 100 3 2 B 110 5 3 C 120 3 4 D 90 7 And in Excel you would normally compute market share by value as 100*Price * Sales / SUM(Price*Sales). Then in R: > d$ShareByValue = 100 * d$Price * d$Sales / sum(d$Price*d$Sales) > d Product Price Sales ShareByValue 1 A 100 3 16.30435 2 B 110 5 29.89130 3 C 120 3 19.56522 4 D 90 7 34.23913 But if the price of Product A was wrong, and you have to change it to 90, then yes, you have to re-run the function: > d$Price=90 > d$ShareByValue = 100 * d$Price * d$Sales / sum(d$Price*d$Sales) > d Product Price Sales ShareByValue 1 A 90 3 16.66667 2 B 90 5 27.77778 3 C 90 3 16.66667 4 D 90 7 38.88889 But now we can get a bit smarter. Let's write a function that computes the share by value: computeShare = function(d){100*d$Price*d$Sales/sum(d$Price*d$Sales)} > computeShare(d) [1] 16.66667 27.77778 16.66667 38.88889 So we just have to do: > d$Share = computeShare(d) and we can re-use that function on other data (with the same named columns). If there are any bugs in that function they only need fixing in one place. Yes, if your data changes then all your columns may need adjusting, but you just write a script or a function that does it. I think something like this: updateSales = function(d){ d$Share = computeShare(d) d$TotalItemCost = totalItemCost(d) d$Tax = taxTate*d$TotalItemCost totalTax = sum(d$Tax) list(data=d,totalTax=totalTax) } is a lot easier to understand than hunting round spreadsheet cells to find that one formula you typed in wrong... Barry ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.