Linked cells, end user input, SUMIFS and create a graph depending on previous

[Hliboi]

Good evening,
I'm trying to build a survey handling file in which the user only needs to fill the database and automatically has access to averages of the itens and automatically create the graph.

I'm having a bit of a nightmare trying to figure this out:

I have one sheet (sheet 1) which the end user fills a name of something which links to another cell in another sheet (sheet 2) that creates a graph. The user has 10 cells max that he can input text.

If he only fills 5 cells leaves me 5 cells that are blank. The 5 cells that are blank enter the graph (problem 1).

The next column has a average ( =SUMIFS(BD!B:B;BD!B:B;"<9";BD!Y:Y;3)/ COUNTIF(BD!Y:Y;3) ) made off a database (sheet 3) and give #DIV/0 because there arent variables made for that in the database that i cant change to #N/A to try to make the graph.

How can i still have a graph without intermission, done automacly based on input from the user (THE PROBLEM)?

How cant i solve this.
Hope i have been clear.

Please help!!!







Sheet 1 ----end user sheet
<table border="0" cellpadding="0" cellspacing="0" height="81" width="103"><colgroup><col style="mso-width-source:userset;mso-width-alt:1206;width:25pt" width="33"> <col style="mso-width-source:userset;mso-width-alt:5302;width:109pt" width="145"> </colgroup><tbody><tr style="height:15.75pt" align="center" height="21"> <td class="xl65" colspan="2" style="height:15.75pt; mso-ignore:colspan;width:134pt" height="21" width="178">Company</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" align="center" height="20">1</td> <td class="xl67" style="border-top:none;border-left:none;width:109pt" align="center" width="145">Alpha</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" align="center" height="20">2</td> <td class="xl67" style="border-top:none;border-left:none;width:109pt" align="center" width="145"> Beta</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" align="center" height="20">3</td> <td class="xl67" style="border-top:none;border-left:none;width:109pt" align="center" width="145">Charlie</td> </tr> </tbody></table>
sheet 2 ---average sheet

<table border="0" cellpadding="0" cellspacing="0" width="286"><colgroup><col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> </colgroup><tbody><tr style="height:21.0pt" height="28"> <td class="xl67" style="height:21.0pt;width:116pt" align="center" height="28" width="155">Alpha</td> <td class="xl73" style="width:98pt" align="center" width="131">4,86</td> </tr> <tr style="height:21.0pt" height="28"> <td class="xl67" style="height:21.0pt;border-top:none" align="center" height="28">Beta</td> <td class="xl73" align="center">5,33</td> </tr> <tr style="height:21.0pt" height="28"> <td class="xl70" style="height:21.0pt;border-top:none" align="center" height="28">Charlie</td> <td class="xl69" align="center">5,00</td> </tr> <tr style="height:20.25pt" height="27"> <td class="xl72" style="height:20.25pt" align="center" height="27"> </td> <td class="xl69" align="center">5,00</td> </tr> <tr style="height:20.25pt" height="27"> <td class="xl71" style="height:20.25pt" align="center" height="27"> </td> <td class="xl68" style="border-top:none" align="center">#DIV/0!</td> </tr> <tr style="height:20.25pt" height="27"> <td class="xl71" style="height:20.25pt" align="center" height="27"> </td> <td class="xl68" style="border-top:none" align="center">#DIV/0!</td> </tr> <tr style="height:20.25pt" height="27"> <td class="xl71" style="height:20.25pt" height="27"> </td> <td class="xl68" style="border-top:none" align="center">#DIV/0!</td> </tr> <tr style="height:20.25pt" height="27"> <td class="xl71" style="height:20.25pt" height="27"> </td> <td class="xl68" style="border-top:none" align="center">#DIV/0!</td> </tr> <tr style="height:20.25pt" height="27"> <td class="xl71" style="height:20.25pt" height="27"> </td> <td class="xl68" style="border-top:none" align="center">#DIV/0!</td> </tr> </tbody></table>

Sheet 3 ----Data from survey - Data base (BD)
<table border="0" cellpadding="0" cellspacing="0" width="2292"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:3291; width:68pt" span="2" width="90"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:3218; width:66pt" span="8" width="88"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:3364; width:69pt" span="4" width="92"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:3364; width:69pt" span="4" width="92"> <col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:16.5pt" height="22"> <td class="xl66" style="height:16.5pt;width:48pt" align="center" height="22" width="64">Nº</td> <td class="xl66" style="width:65pt" align="center" width="87">sat1</td> <td class="xl66" style="width:68pt" align="center" width="90">sat2</td> <td class="xl66" style="width:68pt" align="center" width="90">sat3</td> <td class="xl66" style="width:64pt" align="center" width="85">cm1</td> <td class="xl66" style="width:66pt" align="center" width="88">cm2</td> <td class="xl66" style="width:66pt" align="center" width="88">cm3</td> <td class="xl66" style="width:66pt" align="center" width="88">cm4</td> <td class="xl66" style="width:66pt" align="center" width="88">cm5</td> <td class="xl66" style="width:66pt" align="center" width="88">cm6</td> <td class="xl66" style="width:66pt" align="center" width="88">cm7</td> <td class="xl66" style="width:66pt" align="center" width="88">cm8</td> <td class="xl66" style="width:66pt" align="center" width="88">cm9</td> <td class="xl66" style="width:71pt" align="center" width="94">cm10</td> <td class="xl66" style="width:68pt" align="center" width="90">ecc1</td> <td class="xl66" style="width:69pt" align="center" width="92">ecc2</td> <td class="xl66" style="width:69pt" align="center" width="92">ecc3</td> <td class="xl66" style="width:69pt" align="center" width="92">ecc4</td> <td class="xl66" style="width:69pt" align="center" width="92">ecc5</td> <td class="xl66" style="width:68pt" align="center" width="90">ecp1</td> <td class="xl66" style="width:69pt" align="center" width="92">ecp2</td> <td class="xl66" style="width:69pt" align="center" width="92">ecp3</td> <td class="xl66" style="width:69pt" align="center" width="92">ecp4</td> <td class="xl66" style="width:69pt" align="center" width="92">ecp5</td> <td class="xl66" style="width:74pt" align="center" width="98">Company</td> <td class="xl66" style="width:48pt" align="center" width="64">Platoon
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" align="center" height="17">1</td> <td align="center">5</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">5</td> <td align="center">3</td> <td align="center">3</td> <td align="center">3</td> <td align="center">4</td> <td align="center">2</td> <td align="center">3</td> <td align="center">4</td> <td align="center">4</td> <td align="center">3</td> <td align="center">4</td> <td align="center">3</td> <td align="center">3</td> <td align="center">3</td> <td align="center">3</td> <td align="center">3</td> <td align="center">3</td> <td align="center">1</td> <td align="center">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" align="center" height="17">2</td> <td align="center">6</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">3</td> <td align="center">4</td> <td align="center">4</td> <td align="center">1</td> <td align="center">2</td> <td align="center">2</td> <td align="center">4</td> <td align="center">1</td> <td align="center">3</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">3</td> <td align="center">4</td> <td align="center">3</td> <td align="center">3</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">2</td> <td align="center">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" align="center" height="17">3</td> <td align="center">5</td> <td align="center">5</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">2</td> <td align="center">3</td> <td align="center">2</td> <td align="center">4</td> <td align="center">1</td> <td align="center">3</td> <td align="center">4</td> <td align="center">3</td> <td align="center">4</td> <td align="center">3</td> <td align="center">2</td> <td align="center">4</td> <td align="center">3</td> <td align="center">4</td> <td align="center">4</td> <td align="center">3</td> <td align="center">3</td> <td align="center">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" align="center" height="17">4</td> <td align="center">5</td> <td align="center">5</td> <td align="center">5</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">2</td> <td align="center">3</td> <td align="center">3</td> <td align="center">4</td> <td align="center">1</td> <td align="center">4</td> <td align="center">4</td> <td align="center">3</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">5</td> <td align="center">5</td> <td align="center">5</td> <td align="center">5</td> <td align="center">5</td> <td align="center">1</td> <td align="center">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" align="center" height="17">5</td> <td align="center">6</td> <td align="center">6</td> <td align="center">6</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">5</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">1</td> <td align="center">5</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">4</td> <td align="center">5</td> <td align="center">5</td> <td align="center">4</td> <td align="center">2</td> <td align="center">2</td> </tr> </tbody></table>

 

[Hliboi]

Please Help.

I'll reformulate:

What i need is a formula that can do this:
Check if a cell has input and if not create #N/A so that it doesn't insert in the graph.
On the adjacent column cell i need a formula that calculates the average if (through SUMIFS and COUNTIF -

=SUMIFS(BD!B:B;BD!B:B;"<9";BD!Y:Y;3)/ COUNTIF(BD!Y:Y;3)

)and also changes the #DIV/0 to #N/A so that the graph isn't plotted.

The output would be the user inserts the name of the company, fills out the data in another sheet and automatically goes to a sheet where he/she has the averages and also graphs are plotted automatically.

Was I clear?
Please Help.

​

 

[Hliboi]

Simpler question.

How can I combine the following formulas:

=SUMIFS(BD!B:B;BD!B:B;"<9";BD!Y:Y;2)/ COUNTIF(BD!Y:Y;2)

and

something that If error then #N/A ???????

 

[Hliboi]

Ok i figured this out!

=IFERROR((SUMIFS(BD!B:B;BD!B:B;"<9";BD!Y:Y;5)/COUNTIF(BD!Y:Y;5));#N/A)

The problem is that the graph still plots the #N/A. Now it doenst show 0 instead its just blank.

How to really remove those data points if there is #N/A error???????

Thanks

 

[JD_GFI]

If you are using a pivot chart it should be possible to hide items with no data under the "field settings" properties. Hope this helps.

 

[Hliboi]

Thanks,

But what i really wanted is to create automatic graphics. I have the full area of plot selected and has i insert data the graph creates itself. I'm just having a problem with plotting now.

I use excel 2010 and its plotting the #N/A error making it a column which i don't want.

=IFERROR((SUMIFS(BD!B:B;BD!B:B;"<9";BD!Y:Y;5)/COUNTIF(BD!Y:Y;5));#N/A)

This is working fine. Calculating the average if there is codified user input but when there is no input gives the #N/A error and the graph still plots the point!!!!


I thought it was supposed to disappear if there were #N/A e errors but the data point still appears.


But thanks anyway.

 

[Hliboi]

I got another ideia if anyone could help me.

How can i hide the rows that give error or are blank. And if there is a value show them????

This way i can do automatic graphics based on user input.

Please help.