true power of MS excel. update: PowerBI

[albert3]

Re: MS excel formula, programming, analysis, presentation and tutorials.

boss problema nga yung workbook mo.
nakikita ni excel na meron pang mga value dun sa mga cell sa baba.
remove natin yon

http://www.symbianize.com/attachment.php?attachmentid=1255725&d=1524620423




STEP #1: highlight lahat ng cell na hnd kailangan
http://www.symbianize.com/attachment.php?attachmentid=1255726&d=1524620423


STEP #2: delete lahat ng cell
http://www.symbianize.com/attachment.php?attachmentid=1255727&d=1524620423


Note: Save muna para mg take effect. (para ma refresh[?])

http://www.symbianize.com/attachment.php?attachmentid=1255728&d=1524620423



STEP #3: Export

http://www.symbianize.com/attachment.php?attachmentid=1255729&d=1524620423


Good luck!

Thank you Master! and sa effort!! mwah

 

[botski.®]

Re: MS excel formula, programming, analysis, presentation and tutorials.

mga LODI, gusto ko lang sabihin sa inyo bilib ko sa excel skills niyo
salamat dito.

question lang po, pwede ba ko mag auto sum pag naka filter tas pag select lang ng isang product for example?

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

pede po ata using SUBTOTAL():

kung mag-sa-sum lang, select 109 tapos select ung range na-i-sa-sum.

Goodluck!

 

[kirby21]

Re: MS excel formula, programming, analysis, presentation and tutorials.

pede po ata using SUBTOTAL():


http://www.symbianize.com/attachment.php?attachmentid=1255761&d=1524642269

kung mag-sa-sum lang, select 109 tapos select ung range na-i-sa-sum.
http://www.symbianize.com/attachment.php?attachmentid=1255762&d=1524642269

Goodluck!

glad you're here to help.

 

[khing007]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Nice, pa book po muna, aralin ko later, thanks.

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

glad you're here to help.

Sobrng thankful po s inyo sir. Shnshare ko lng ung mga natutunan ko sa tutorials nyo.

 

[voyage18]

Re: MS excel formula, programming, analysis, presentation and tutorials.

sir romcel thanks,,

Sir pa check ng file ko. baka may mas maganda pa po.. okay po ang ginawa nio sir...

up lang po thanks

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@voyage,
boss sakto katatapos ko lang mag-update
DOWNLOAD HERE

sabihan mo agad ako kung gumana sir


Logic nung workbook:

matList:
1)listahan ng lahat ng raw materials na i-a-account mo.
2)ilagay ung mga critical values nila.
-para kapag kinuha nung summary tong values dito, ma-checheck kung kailangan na bang bumile/mag-restock.


menuList:
1)ilista ung mga dishes offered sa canteen mo.
2)ilista din kung ano ang ingredients non.
3)ilista kung ilang units(kgs/grams) per dish.
4)summary related:[nakabase sa dateStart at dateEnd ng inventory]
4a)number of dishes ordered. kung ilang orders ang na commit for that period of time.
4b)amount(refer to orderList) total amount ng lahat ng orders.
-hindi to parepareho baka kasi ng bibigay kayo ng discount sa mga PWD/elederlies or sa mga taong tumulong sa workbook mo
4c)percentage per serving. [disclaimer:hnd ko alam kung tama or neccessary ito]
4d) earnings per dish

NetWeightOfDish = [(units consumed for item1), (units consumed for item2), ...,(units consumed for itemN)]
SumOfSalesPerDish = [total ng benta sa bawat dish]
PercentagePerServing = [(1/NetWeightOfDish)*UnitsConsumedForItemN]


rcvdList:
1)dito papasok ung mga items na binili/diniliver.
2)listahan ng mga item na na-i-restock mo.
3)listahan ng presyo per unit.
-d naten alam baka magbago presyo depende sa araw at supplier. so ma-ki-keep naten un sa records.


orderList:
1)listahan ng mga order na na-commit on a specified date.
2)listahan ng orders per table number. [pwede gmtin para mlman kung anong madalas pwestuhan ng costumer?]
3)qty ng mga orders.
4)amount[dito nka base ung (amount sa menulist)]. for discount purposes. or kung biglaang magbago presyo per dish.
-hindi dpat ma-apektuhan ung mga prices/sales prior sa adjustment ng prices.


SummaryInventory:
1) input mo ang ung dateStart at dateEnd ng inventory.
-siguro i-a-accept neto kahit 1) Annual 2)Semi-Annual 3)Quarerly 4)Monthly 5)Weekly. Input lang ung correct dates
2)ColumnC = Begbal, beginning balance nung inventory.
3)ColumnD = Rcvd, qty ng mga nareceive/napurchase between dateStart and dateEnd.
4)ColumnE = Used, total na nagamit na weight ng bawat ingredient. depende kasi sa dish kung gaano ka taas ang pagconsume.
5)ColumnF = Rem, kung ilan n lang ang natitirwang weight ng bawat ingredient. [C + D] - E
6)ColumnG = Critical, kung ano ang nka set na critical value for that item. (para lang maktia naten agad)
7)ColumnH = forPurch, qty kung ilan ang for purchase para hnd maging critical ung value ng ingredient.
8)ColumnI = costPurch, kung magkano nagastos dun sa mga items na na purchase na [D] * unit price
9)ColumnJ = earning_per_item, kung magkano ang kinita per ingredient
Note: EarningsPerIngedient = SumofSalesPerDish * PercentagePerServing
10)ColumnK = actual_earning, [J - I]

 

[dreadstar]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Question mga masters. I have this accounting formula that I want to input into excel.

Total = Base + VAT

wherein the formulas are:

Total = Base + Basex0.12
Base = Total / 1.12
VAT = Basex0.12
EWT = Basex0.02

Normally the value of Total is given and I can compute the other variables. However if another variable's value is given (ex Base), the other variables (Total, VAT and EWT) can be computed and so on.

How do I encode this into excel so that I can input the value of one of the variables and it will automatically compute for the others?

If someone could point me to the right direction, I can google and research on it.

Thanks in advance.

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

That formula is circularlly referenced.
We are talking about procurement prices right?

 

[dreadstar]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Not sure what circularly referenced means but only one variable (out of the 4) should a value be inputed and the excel sheet should compute the other 3 variables.

Yes these are procurement prices. Normally I have the Total price and I have to compute the other 3 variables. However at times, I have the Base price or EWT price and I need to work my way back to rerieve the other variables.

 

[lrdfrozen]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hello guys ginawa ko lang tong thread para sa mga kasymb natin na need ng tulong about MS excel. mga reporting staff, encoders, data analyst, secrectary, or Ms excel systems. pasok po kayo dito. share tayo ng knowledge. i will provide tutorials too. believe me after learning this things after a month i got my promotion to go up another level from our company. so try it now. made your bosses like their jaws drop.​

on my first example. you will see chart with controls like the picture below.
View attachment 887637

here you will see conditional formatting with toggle.like the picture below.
View attachment 887650

now you will see a picture album using excel. but in this tutorial i'll give you an example of animal album.
View attachment 888228

in this picture you will see a chart that looks like a temperature. it would be helpful to a presentations with a sales and target sales.
View attachment 888874

now in this picture you will see a speedo meter chart with control. the tutorial was provided from the attachment.
View attachment 888895

at eto naman ang sample dashboards ko

sample 1
http://i.giphy.com/xTiTnzLUI4whphKpoc.gif

sample 2
http://i.giphy.com/3o85xkmN4eEdkNV6Zq.gif
​

if nagustohan nyo po. pwede nyo sya idownload sa baba.

digital clock by dhok si...

​

sana po magshare din ang iba,


grading system excel application upload by our contributors. http://www.symbianize.com/showthread.php?t=1132342&p=19858679&viewfull=1#post19858679

eto na po para yung iba di na masyado magtanong sa excel tricks. lalo na mga charts with controls. im giving you all this.

http://i.imgsafe.org/19867c1.jpg

Excel hacks.rar


eto po splitted into two parts. thanks to devildarkrs
part 1
http://www.symbianize.com/attachment.php?attachmentid=1043979&d=1436253924
part 2
http://www.symbianize.com/attachment.php?attachmentid=1043978&d=1436253924
credits to creator of the book.

HOW TO UNLOCK VBA PROJECT
credits goes to athenesia​

thanks for this information. it help me

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Not sure what circularly referenced means but only one variable (out of the 4) should a value be inputed and the excel sheet should compute the other 3 variables.

Yes these are procurement prices. Normally I have the Total price and I have to compute the other 3 variables. However at times, I have the Base price or EWT price and I need to work my way back to rerieve the other variables.

In this case i would prefer a dynamic formula switched over with a dropdown menu.
I dont have ms excel with me at the moment, do you need this ASAP?

Edit:would you mind me having a peek at the workbook you are working with? ?

 

[dreadstar]

Re: MS excel formula, programming, analysis, presentation and tutorials.

In this case i would prefer a dynamic formula switched over with a dropdown menu.
I dont have ms excel with me at the moment, do you need this ASAP?

Edit:would you mind me having a peek at the workbook you are working with? ?

Thank you for the help. No I dont need this ASAP.

If you look at the attached file, I can put a value for TOTAL and excel would compute the other 3 variables (BASE, VAT and EWT) automatically. However, take for example if BASE is the given value, then I want the other 3 unknown variables (TOTAL, VAT and EWT) to be computed.

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@dreadstar,
i've attached the updated workbook and you can download it here.

Offtopic:
Keeps me thinking, does the variable Total represents the totality of the purchase order?
Won’t it be a problem if there are included items which EWT is not applicable?

 

[dreadstar]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@romcel11

Thank you! Your solution works great. I just had to improve on it using a Nested IF for a 4 item drop down menu (improved your 2 items list menu).

Yes the Total represents the whole purchase order. Different percentage EWT applies to different companies or whether you sell goods or services. It would not be a problem because each company has one EWT rule application. Each purchase order only applies to one company hence there is no conflict and no multiple EWT rule will apply.

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Its nice to hear that it (partially) worked for you.


OFFTOPIC:
I am assuming that you will use this formula as the supplier, and you are not having further difficulties.
But it really ticks me off, so please pardon me for insisting this arguement.

It would not be a problem because each company has one EWT rule application.

I am looking at this from the client's side.
Let's consider that I am about to buy an item you offer and avail your technical assistance, say installation (with charges).

I'm entitled to avail both 1% for the item and 2% for the service, right?
Please correct me if I am wrong.

 

[dreadstar]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Its nice to hear that it (partially) worked for you.


OFFTOPIC:
I am looking at this from the client's side.
Let's consider that I am about to buy an item you offer and avail your technical assistance, say installation (with charges).

I'm entitled to avail both 1% for the item and 2% for the service, right?
Please correct me if I am wrong.

If I supplied only the goods without service (installation) then the ewt is 1%. I will issue a Sales Invoice and you will give me a 2307 representing the deducted 1% tax.

If I supplied both goods and service (installation) then the ewt is the higher among the two which is 2% for the service. I will issue an Official Receipt and you will give me a 2307 representing the deducted 2% tax.

 

[chester92]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hello po.. Mga sir, paturo sana ako kung paano po gawin sa excel ang ganitong BORDER at yung nakFREEZE po yung row 1-4..?

Maraming Salamat po..

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

FOR FREEZE PANES:

1: Saan nag-be-base ung freeze pane:

sa activecell or sa selected mong cell sya naka base. mafe-freeze sya sa upper-left part ng activecell.
so bago mag-freeze ng pane. iselect muna kung saan nyo gustong ifreeze

2: Ifreeze na ang pane:

3: Output:

eto ung magiging itsura.

4: In your case:

at sa case mo po. eto ang se-select naten. tapos dito naten i-activate ung freeze pane.



FOR BORDERS:

1: Select ung mga cell:
2:Other borders:

sa ribbon. hanpin un font group. and look for this button. click dropdown menu.
may lalabas na pop-up menu


3: Follow steps:

GOODLUCK PO!