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? Thanks. Kind regards, Lingyi [[alternative HTML version deleted]]
On Mar 4, 2013, at 5:39 AM, Tammy Ma 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?When this request has appeared before, the advice given was to look at RExcel. That is only an option in the Windows environment. -- David Winsemius Alameda, CA, USA
On Mon, Mar 4, 2013 at 1:39 PM, Tammy Ma <metal_licaling at 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
This request is very completely satisfied by RExcel. Please look at rcom.univie.ac.at for full information including download information. Follow up should be on the rcom email list. You can sign up at the website. On Mon, Mar 4, 2013 at 8:39 AM, Tammy Ma <metal_licaling@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? > > Thanks. > > Kind regards, > Lingyi > > > > > [[alternative HTML version deleted]] > > ______________________________________________ > 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. >[[alternative HTML version deleted]]
Maybe Matching Threads
- How to make the labels of pie chart are not overlapping?
- How to list the all products' information of the latest month?
- Change directory to implement same programes
- How to search in each excel in my working dir and extract part information of one product in each country?
- how to aggregate the dataset