Microsoft Excel - Gross marigin Formula - Asked By anu anu on 07-Oct-14 01:10 PM

i want blelow sumif formula convert to gross margin formula ,

=SUMIFS(tblSales[Cost],tblSales[Year],$C$3,tblSales[Product],$B9,tblSales[Region],C$8).

like i have Cost And revenue filled , i want above formula convert in =rev-cost\Rev with same criteria

Have you tried?

=(SUMIFS(tblSales[Rev],tblSales[Year],$C$3,tblSales[Product],$B9,tblSales[Region],C$8) - SUMIFS(tblSales[Cost],tblSales[Year],$C$3,tblSales[Product],$B9,tblSales[Region],C$8))
/ SUMIFS(tblSales[Rev],tblSales[Year],$C$3,tblSales[Product],$B9,tblSales[Region],C$8)

Need your help for making one report, actually   i want gross margin report of 70 account of 3 branches and onwards jan-13,. i have one file but i tried to convert to according to my requirements but some error is coming

i have attached the dummy file . plz help me out.

interactive-sales-chart.zip.

Hello anu,
I have modified your file.  I hope this goes some way to getting what you want.
interactive-sales-chart_a.zip
Regards
Harry

yes it`s working  but in chart it`s show not clear  is there any way out to shows clear pics.

Hello anu,
The problem is the number of products that you are trying to show.  In the original there were only six, you are trying to show many more so to fit them into one chart means that the space available is much less. 
To show them all on one chart and be more visible you would need to make the area much larger and then it would not fit on one screen.  The alternative is to show fewer products or change to show regions and select individual products.
Regards
Harry

 hi ,
 harry
 if product only 33 than how to 

Increase the chart area .

thanks anu
 

hi harry ,

now i make this report not product wise , but engeer wise and  branches have ,12,6, and 17 engineer .

is there any scope to make this chart clear.

thanks
anu

hi harry ,

i have some changes in this file , but now i am facinf one problem , now in my data i have 3 branch like geeta neena poonam now in 3 branches having some engineer

geeta --12

neena -6

poonam *17

now calculation file i have changed and it`s working fine . i want in chart when i seclect geeta in chart shows only geeta engineer data , and when neena shows neena`s engineers data.

thanks
 anu

Hi anu,
Here is the file with reduced number of variables and larger area,
interactive-sales-chart_b.zip
In terms of changing the variables plotted you have to change the selection criteria on the calculation sheet in the data for chart area.  Without your actual data, I can't be more specific than that.
Regards
Harry

thank you  harry ,

 interactive-sales-chart_b.zip.

i need . when i seclect geeta only geeta`s product is showing in chart .

regards
 usha

Hello anu,
Have a look at this.
interactive-sales-chart_bb.zip
Regards
Harry

hi harry ,

and once agin thank you..

 harry . can we do some in this file. like this which i have done . but one problem is there , product name not change . other wise % is change according to product wise.

interactive-sales-chart_bb.zip

regards
anu

Hello anu,
interactive-sales-chart_bc.zip
Regards
Harry

thank you , yoy are best harry

thanks
anu

hi harry ,
if i want 2 region in chart , is it possible , plz help. calculation sheet i have done but in chart it`s not showing


regards
anu

Hello anu,
You would have to add new data ranges to the chart and probably adjust the spacing and overlap of the columns to allow them to show.
If you can't manage that, post a file and I will see if I can have a look at it.
Regards
Harry

interactive--chart_bc stores.zip

hi harry , have look

regards
usha

Hello usha,
I hope you are learning something from all this.
interactive--chart_bc stores.zip
Regards
Harry

. hi harry ,
 i tryed .
but in this file one error is coming.and also in chart 2 region is showing in single chart like when we select pooja poonam is also show

New Microsoft Office Word Document (6).zip

and more thing can you tell how increase chart range and what i am doing worng in file .

thanks regards
anu

 

Hello anu,
The reason for the error is that the data is incomplete and it is trying to chart cells with divide by zero errors.
Regards
Harry

thank you harry , now it`s done
 but in chart showing of  1 ,2 ,3 . % baar but i need product name wise baar % .
like
New Microsoft Office Word Document.zip

anu

Sorry anu,
I have no idea of what you mean.  The category axis reflects the data that is in the column labeled products on the Data page.
Regards
Harry

harry ,

i mean , in chart two lstRegions showing. i want when i select region chart must be show only perticuler region data.

Regards
Anu.

Hello Anu,
Perhaps if you manually generate some charts to show me what exactly it is that you want to see then I might be able to help.
Regards
Harry

interactive- (4).zip

hi harry ,

have a look .

 i want whent i seclect geeta in chart only geeta data is showing .

anu

Hello anu,
The problem was that your new data was in the negative range and the  sorting algorithm only worked in descending order.  I have set it up so that it should work for either case but I can't guarantee that all possible cases will be handled successfully.  I think you should work to try to understand how the system works so that you can do something to sort these things out yourself.
interactive- (4).zip
Regards
Harry

hi haary ,,

anu--chart_bc (2).zip
in this file i have done some changes , but i thing i made some mistake. in kol and chd branch not calculating in chart .

plz help

thanks
Anu

Hello Anu,
interactive-chart_anu.zip
The problem was with the new columns that you generated in F and G on the calculations sheet as unfortunately the table references are not fixed and the formulae were not referring to the correct columns on the data sheet.  I also rescaled the data for the chart so the the results mostly appear within scale on the chart.  This adjustment is in column V if you want to revisit that but I would suggest that the problem needs to be addressed in the original data/calculations or the ranging of the chart.
Regards
Harry