true power of MS excel. update: PowerBI

[john1206]

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

Good eve mga Sir/Mam,

Patulong naman sa formula or code sa excel.

Gusto ko sana mangyari sa may may sheet na MONTH GRAPH magkaroon ng search button para sa another sheet DATA (name) once na ma search ko na halimbawa si A lalabas yung chart nya base sa month sa sheet DATA pero ang kondisyon is yung may status na OK lang.

See attachment po..

Thanks po and more power...

pwede ka na dyan mag PivotTable Chart

- - - Updated - - -

Pa help naman guys.. pano ko gagawan ng tamang formula etoView attachment 1264914View attachment 1264915

- - - Updated - - -

meron po bang FACEBOOK group about sa microsoft excel tips

na-solve mo na? kung di pa pengeng copy ng file

 

[romcel11]

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

@raffy, sir ano po password ng rar? solved na to

@ayeshaaron,
eto na po ung new workbook nyo.
CLICK HERE TO DOWNLOAD


@lostone,

tried recreating your workbook. assume ko lang na eto yata ung dapat na formula?

=SUM(($L7:L8)*D$1,($P7:P8)*E$1,(T7:T8)*F$1)

na dapat ay entered with CTRL + ALT.Like this

 

[ayeshaaaron]

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

Sir Romcel11 thanks po...more power

 

[jayvin0016]

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

Boss Romcel11 makikisuyo pa ko ulit ng isa eto mejo mas mahirap pero kunsakaling kailangan ng mahaba habang paliwanagan pwede ko naman ipaliwanag kung meron man di malinaw.

Maraming salamat boss

 

[romcel11]

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

Queries..
1) kung sa alchemy ng fall ung ung sample. say 10 linear meter ung sample. so alchemy 25000 ang gagamiting box.
bakit ang ginamit na values sa pagkuha ng volumetric wieght ay ung dimension ng alchemy 25001?

2) bakit isang side lang ng sample ang merong dimension? yun na ba yung pinaka malaking dimension nung sample?
posible ba na magkaroon ng sample na hindi rectangular prism ang shape?
kasi nga pala documents ung pinapadala. pedeng i-roll no?

3) anong value ang gagamitin sa pagsearch sa 'Range (kg)' ung volumetric or ung actual? Or kung saan naka-base ung Freight Cost?

tama po ba na.....
1) ang end goal ng workbook na to ay to compute for the freight cost?

2) for price comparison po ang purpose naten kaya tayo ng ko-compute for fedex vs tnt?

So far eto ung naintindihan ko...
(correct me kung mali ako)

[P] Weight of Item
[Q] Weight per R or M
[R] Total Weight = P * Q

[A] Freight Cost
Fuel Surcharge = [A] * 1.16
[C] ESS = R * 0.25 <~ no less than 2.5
[D] Insurance
[E] Delivery Fee
[F] Total Air Freight Cost = A + B + C + D + E

pa help po sa pag-decide kung ano ang kailangan mong data na pang-input.
a) ung dimension nung sample
b) ung box na ire-recommend sa sample
c) none of the above.

 

[jayvin0016]

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

Queries..
1) kung sa alchemy ng fall ung ung sample. say 10 linear meter ung sample. so alchemy 25000 ang gagamiting box.
bakit ang ginamit na values sa pagkuha ng volumetric wieght ay ung dimension ng alchemy 25001?
- Boss bali itong transport calculator na to is for orders nap o mismo to which ang mga item kasi is either sold per roll or per Linear Meter. Kung per Linear meter or meter ang item may maximum length lang which is for example sa alchemy 25000 (see pic1.png)


2) bakit isang side lang ng sample ang merong dimension? yun na ba yung pinaka malaking dimension nung sample?
posible ba na magkaroon ng sample na hindi rectangular prism ang shape?
kasi nga pala documents ung pinapadala. pedeng i-roll no?
hehe.. lahat naman ng ordered wallpapers sir niroroll nila tapos nilalagay sa box yung mga sold per meter poi sang buong roll yan pinapasok sa box pero pag per ROLL naman ang benta nung wallpaper it means fixed yun isang roll pero kaya kasing magpasok ng 4 or 6 rolls minsan sa isang box.

Explanation: pag per meter kasi fixed width yung wallpaper pero different length so kunyari 10 meters at per roll nga ang benta isang mahabang roll yan ng 10m na walang putol malalaman pong per roll or per meter based dun sa field na QTY/BOX kasi naka indicate dun pag M per Meter pag R per Roll.


3) anong value ang gagamitin sa pagsearch sa 'Range (kg)' ung volumetric or ung actual? Or kung saan naka-base ung Freight Cost?
Sa value po ng gagamitin boss kailangan kasi computin for example sa alchemy 25000 10 meters and ACTUAL po nya is 0.17kg per meter (0.17x10=1.7kg) at yung Volumetric naman (18x18x95 box dimension / 4000 (volumetric weight factor) = 7.695) kaya 7.695 ginamit or rounded off to 8kg kayabased sa rate table ng TNT kung magkano yung rate nila ng 8kg dun iccompute yung freight.

tama po ba na.....
1) ang end goal ng workbook na to ay to compute for the freight cost? Transport cost boss kasama na yung Freight+ESS+Insurance+admin+delivery fee

2) for price comparison po ang purpose naten kaya tayo ng ko-compute for fedex vs tnt?
No need na siguro ng price comparison boss sa ngayon TNT rate lang gagamitin since Belgium kasi to nangagaling eh kaya TNT rate lang po iba lang kasi ang charge ng TNT pag Express at Economy Ang express kasi 10kg below pag umakyat na sa 11kg Economy na ang magandang gamitin na rate.

So far eto ung naintindihan ko...
(correct me kung mali ako)

[P] Weight of Item = weight to be used can be determined which is greater Actual vs. Volumetric
[Q] Weight per R or M = to get the actual weight
Quantity/Box= to get the volumetric weight
[R] Total Weight = P * Q

[A] Freight Cost
Fuel Surcharge = [A] * 1.16 - tama po
[C] ESS = R * 0.25 <~ no less than 2.5 - tama po
[D] Insurance <~ no less than 30
[E] Delivery Fee
[F] Total Air Freight Cost = A + B + C + D + E - tama po

pa help po sa pag-decide kung ano ang kailangan mong data na pang-input.
a) ung dimension nung sample
b) ung box na ire-recommend sa sample
c) none of the above.

- ang gusto ko sanang mangyari boss is yung mga items/references isselect lang then ilalagay yun qty kung ilan tapos automatic sana makapagbigay ng transport cost

 

[romcel11]

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

@jayvin, eto na new workbook mo. pero for checking pa yan sir.
plus ni remove ko ung ibang items. pa update n lang ulit. copy paste lang naman yan

CLICK HERE TO DOWNLOAD

 

[lukeskywalker06]

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​

Maraming SALAMAT !

 

[jeeceemaming]

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

mamaw sa galing

 

[jayvin0016]

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

Boss Romcel 11 gumawa po ako ng 1 pa na example para sa computation tsaka explanation kung pano po nagccompute baka sakaling magawan mo ng paraan boss.

bali 2 references lang muna ang nilagay ko kailangan kasi maipaliwanag ko maigi sa inyo yung gusto ko mangyari para mas madali po magawan ng sistema kasi alam ko komplikado tlga yung pagccompute nito at masakit sa ulo kasi andaming components

Maraming salamat

 

[romcel11]

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

@jayvin, na check mo na ung workbook? ok ba sya or totally mali sya?

 

[jayvin0016]

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

@Romcel11 boss yung sa Rows 1-11 mejo di ko po magets kasi kung san nakukuha yung values pero parang di po ganon yung pagkuha/pagcompute. pasensya na boss mejo masakit kasi tlga sa ulo yung computation kasi

 

[romcel11]

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

@jayvin, sige lang.. kaso bka matagalan din to ng sobra. gnto na lang...
mag upload n lang ako ng kapira-kapiraso ng progress.
para mabantayan mo ung excel file.

 

[jayvin0016]

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

@Romcel11 sige boss mas ok po yun atleast kung may mali e makita po agad. yung nasa worksheet ko boss 2 reference pa lang yan dadagdagan ko na lang pag nakuha natin yung tamang formula at computation. Salamat

 

[hephooray]

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

Mga master pa help naman pano po gawin flashing yung value ng cell kung yung value nya ay mas mababa na sa isang cell value. may formula po kasi ako. salamat

 

[dadiming]

Re: MS excel reportings, presentations, and vba helping thre

thanks po nito ts, bm muna

 

[romcel11]

Re: MS excel reportings, presentations, and vba helping thre

@hephooray, attach ka ng sample workbook

 

[hephooray]

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

Ito po sir yung attachedment paki check nyo na lang po. alam ko po sa vba macro pero hindi ko po ma figure out kasi may formula na po ako existing dun sa cell. sana po matulungan nyo po ako.

 

[romcel11]

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

@hephooray, pre hindi naman nid ng vba. bka mas trip mo ung Conditional Formatting na built-in excel feature.




CONDITIONAL FORMATTING

STEP 1
Select mo ung row or (in this case) column.

STEP 2
Click 'CONDITIONAL FORMATTING' > 'HIGHLIGHT CELL RULES' > 'LESS THAN..'.
merong mag popout na window.

STEP 3
Set ung value.. dito icocompare ng excel at kung mas mababa dito ang value ng bawat cell na selected
ay i-ha-highlight nya.

at eto na ung workbook mo.

click here to download

kung hindi eto ung hanap mo. try natin gawan ng paraan.
ung flashing na gusto mo ba ung ng-bi-blink blink blink talaga?

 

[derpssss]

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

Salamat dto boss.. Dag dag laman sa utak ko to hehehe