Excel III: Customizable laptop app

Hello for the last time in this term. The last excel practice we've made consists on an application to make a laptop with the components you choose, which gives you a budget and the possibility to adjust a price limit.

The first sheet is a front page with linking buttons to the rest of the sheets, as always.

The components' sheet is a simple one but it takes some time and it's such a tedious work because you have to look for information of laptops' components and their prices. The only remarkable thing here is the format of the price cells, in euros, and the linking buttons to the other sheets.

The invoice sheet is very complex. For the price of each component I set a "VLOOKUP" function, which I explained in the post about the automatic invoice, with this code: =BUSCARV(C4;Componentes!B2:C13;2;FALSO). To make a dropdown list for each component I used a data format called data validation, as a list, taking the data from the respective chart from the components' sheet. For the final price I added all the components' prices and then I added the 21% of taxes as you can see in the image. And as alert I added an 'IF' function which warns you if the final price exceeds your personal price limit, and which is =SI(C14=0;"";SI(D17>C14;"CUIDADO, ¡SUPERADO!";"NO SUPERADO AÚN")). 

To make it become red I added a conditional format only applied to cells with the specific text that is shown when the personal limit price is exceeded. But to alert you in a greater way I also added an error message through another data validation in the price limit cell. Here you have to make possible only a lower final price amount than the price limit amount, as the image below shows, and set your own error message.

I hope it was useful, you can always ask me anything in case of doubt. Have a great Christmas time and enjoy yourselves on this holydays!

Excel II: Ecuation solver

Hello again. The second big project we've made with excel is an ecuation solver which can solve simple ecuations of first degree and of second degree quickly.

In the first sheet there must be a front sheet that links to the rest of the sheets: 1st degree ecuation, representation of the 1st degree ecuation, 2nd degree ecuation and representation of the 2nd degree ecuation. You can do it with a macro or with a hyperlink as the image shows.

For the first degree ecuation I just had to write the ecuation leaving gaps where each term had to be written and leave a solution gap where I wrote the formula to solve the ecuation. I also made it notify if there were terms left. So my function was =SI(D7=0;SI(F7=0;"¡Faltan coeficientes en la ecuación!";"¡Faltan coeficientes en la ecuación!");(H7-F7)/D7). I solved the ecuation leaving the x alone.

For its representation I wrote the formula as in the previous sheet and gave several values for the x in a chart and, to obtain the respective value of y, I just made a formula replacing x by the value given respectively: =SI(C10=" ";" ";$D$5*C10+$F$5-$H$5). To add the graphic you insert a lines graphic and set the data as the image shows. I also added linking buttons as in all the sheets.

And for the second degree ecuation it's the same but with a different formula: =SI(D7=0;SI(F7=0;SI(H7=0;"¡Faltan coeficientes en la ecuación!";"Ecuación incompatible");"Ecuación de primer grado");SI((F7^2-4*D7*H7)>=0;((-F7+(RAIZ(F7^2-4*D7*H7)))/(2*D7));"Sin resultados reales")) for the first result and: =SI(D7=0;SI(F7=0;SI(H7=0;"¡Faltan coeficientes en la ecuación!";"Ecuación incompatible");"Sólo hay un resultado");SI((F7^2-4*D7*H7)>=0;((-F7-(RAIZ(F7^2-4*D7*H7)))/(2*D7));"Sin resultados reales")) for the second result. 

To make its representation it's the same, replace the x by different values to obtain the y, and create the graphic with that values. The formula I used was: =SI(C10="";"";$D$5*(C10^2)+$F$5*C10+$H$5). 

I made erase and print buttons with macros in each sheet so, if you want to know how I did them, visit my previous post. I also added linking buttons in each sheet to link to the rest of the sheets.

I hope it was useful and if you've any questions, please ask me. See you.