true power of MS excel. update: PowerBI

[bytesize]

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

sir patulong naman kun nu formula for computation on cash gift sa excel. eto po yun terms, kun from appointment date nya e naka 4 mos na sya as of october, then 5k mkkuha nya. if 3 mos-2500, 2mos-1500, 1mo-1000, below one month-500

maam ito ang formula:

=INDEX(E2:F6,MATCH(1,--(MONTH(B2)-MONTH(A2)>=E2:E6),0),2)

View attachment 290757


kailangan po i-enter ang formula using control + shift + enter

 

[bluefox]

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

excel gurus, exercise muna.

View attachment 1160485

how many wins do i have to make to get a win percentage of 80%
gawa kayo ng formula na draggable from cell A3 to A5. No goalseek please. It has to be a formula. Ang makaka-solve, ay papakiusapang sumali sa #excelchampPH hehehehe.

==================

tama ba sir

 

[bytesize]

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

Sir patulong naman. tks

View attachment 1160494

paano ang formula nito.

Select rows na gusto mo i highlight>>HOME>>Conditional formatting>> New rule >> Use formula to determine bla bla>>formula: =d2<>e2

 

[bluefox]

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

Select rows na gusto mo i highlight>>HOME>>Conditional formatting>> New rule >> Use formula to determine bla bla>>formula: =d2<>e2

ok sir try ko.
pwede ba i-drag ung formula or per row ko sya gawin? tks

 

[bytesize]

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

tama ba sir

beri good. ngayon papakiusapan na kitang sumali ka na ng excel champ open pa yung qualifying heat. tell me pag tapos ka na sa chart i lilike ko, ilike mo din yung akin. hehe

 

[bluefox]

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

beri good. pero may mas efficient pa. nagrerely yan sa rounding up of integers. haha ngayon sumali ka na ng excel champ open pa yung qualifying heat. tell me pag tapos ka na sa chart i lilike ko, ilike mo din yung akin. hehe

sige sir, pero meron mas madali na formula dyan, para naman madagdagan kaalaman. tks

 

[bytesize]

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

sige sir, pero meron mas madali na formula dyan, para naman madagdagan kaalaman. tks

kase pag ni drag mo yung formula mo sa a2 error na at good lang sya for 80% =) mamaya sabihin ko iba pang solution - pero tama yang ginawa mo. ako yung mali sa specs ko dapat sinabi ko dynamic formula good for any percentages. ako yung mali. hehe.

 

[dianajane]

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

maam ito ang formula:

=INDEX(E2:F6,MATCH(1,--(MONTH(B2)-MONTH(A2)>=E2:E6),0),2)

View attachment 1160493


kailangan po i-enter ang formula using control + shift + enter

IDOL!
Pnu po pag issali sa issali sa formula yung mga weekends and holidays?
and triny ko din po sya from dates 9/1 - 10/31 which is 2mos kung iccompute pero yung pang 1month padin ang nagaappear

 

[bytesize]

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

ok sir try ko.
pwede ba i-drag ung formula or per row ko sya gawin? tks

waaahh ikaw din pala yung naka solve nung exercise hahahaha naduling na ako kakasagot ng tanong dito at sa mga nagtatanong saken sa fb ng excel problems. sensya na sir.

bluefox eto steps: select mo yung buong table except headers >> home >> conditional formatting >>new rule>> use formula ek ek..>> formula: =$E2<>$D2

View attachment 290768



- - - Updated - - -

IDOL!
Pnu po pag issali sa issali sa formula yung mga weekends and holidays?
and triny ko din po sya from dates 9/1 - 10/31 which is 2mos kung iccompute pero yung pang 1month padin ang nagaappear

paano nyo po ba kinu compute ang 1 month? 30 days lang ba? ang 9/1 - 10/31 ay pasok pa din sa 1 month. magiging 2 months sya sa 11/1

if 30 days ang basehan ito yung formula:
=INDEX($E$2:$F$6,MATCH(1,--((DAYS360(A2,B2,FALSE)/30)>=$E$2:$E$6),0),2)
again enter using control+shift+enter

 

[aubrey03]

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

excel gurus, exercise muna.

View attachment 1160485

how many wins do i have to make to get a win percentage of 80%
gawa kayo ng formula na draggable from cell A3 to A5. No goalseek please. It has to be a formula. Ang makaka-solve, ay papakiusapang sumali sa #excelchampPH hehehehe.

==================

Sali ako

Solved using algebra:

A3 / (A3+B3) = 0.8
A3 = 0.8 (A3 + B3)
A3 = 0.8A3 + 0.8B3
A3 – 0.8A3 = 0.8B3
0.2A3 = 0.8B3
A3 = 0.8B3/0.2

View attachment 290802

 

[bytesize]

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

Sali ako

Solved using algebra:

A3 / (A3+B3) = 0.8
A3 = 0.8 (A3 + B3)
A3 = 0.8A3 + 0.8B3
A3 – 0.8A3 = 0.8B3
0.2A3 = 0.8B3
A3 = 0.8B3/0.2

View attachment 1160577

beriii goood hehehehe algebra is the best approach.

- - - Updated - - -

Another exercise boys and girls.

View attachment 290822

Gusto mangutang ni Juan sa banko. Ang monthly budget nya for amortization ay nasa E2 - E5 gawa kayo ng formula (D2-D5) na magsosolve ng Tenure Term (in years) dapat piliin nya para pasok sa budget nya ang monthly amortization. No Goalseek. Formula should be draggable.

 

[kirby21]

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

excel gurus, exercise muna.

View attachment 1160485

how many wins do i have to make to get a win percentage of 80%
gawa kayo ng formula na draggable from cell A3 to A5. No goalseek please. It has to be a formula. Ang makaka-solve, ay papakiusapang sumali sa #excelchampPH hehehehe.

==================

column B multiply to 4 View attachment 290826

- - - Updated - - -

beriii goood hehehehe algebra is the best approach.

- - - Updated - - -

Another exercise boys and girls.

View attachment 1160605

Gusto mangutang ni Juan sa banko. Ang monthly budget nya for amortization ay nasa E2 - E5 gawa kayo ng formula na magsosolve ng Tenure Term (in years) dapat piliin nya para pasok sa budget nya ang monthly amortization. No Goalseek. Formula should be draggable.

patay tayo dyan. di ako marunong sa mga loans. di ko nga alam ang amortizations eh. hahaha

offtopic mathematician ka boss? at ano fb mo? para dun matulungan din kita sa mga inquiries. like the way your doing here.

thanks

 

[bytesize]

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

column B multiply to 4 View attachment 1160609

- - - Updated - - -



patay tayo dyan. di ako marunong sa mga loans. di ko nga alam ang amortizations eh. hahaha

offtopic mathematician ka boss? at ano fb mo? para dun matulungan din kita sa mga inquiries. like the way your doing here.

thanks

nakow hinde. passionate lang sa excel. hehe. yung nagtatanong saken sa fb mga former colleagues ko nagpapatulong sa spreadsheet probs nila. pero mukhang magandang idea yan. sir, bat di ka gumawa ng fb group para sa Excel inquiries ng mga noypi. hehe. mas madali sumagot sa fb.

 

[Prinsipe828]

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

Pasubscribed po sa thread.

 

[bytesize]

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

Guys, solution for the PMT problem is at : https://docs.google.com/spreadsheets/d/1NLpwAVb4a-yQHotjbDObaVO9Oa-LWaGLx_y_LWYmTbY/edit?usp=sharing
Googlesheets behaves pretty much the same as Excel. So if you have burning questions in Excel, you can just create a sheet there name it accordingly and ask it here. Mas madali kase mag solve ng spreadsheet problems pag may sample worksheet. thanks!!

- - - Updated - - -

Pasubscribed po sa thread.

sir if you have Excel questions, don't hesitate to ask.

 

[dannyps2]

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

mga sir, ask ko lang gusto ko sana 10 digit serial number sa column A mag display pag nag fill in sa column B. pano ba gawin to? salamat po!

 

[kirby21]

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

based on computation mo gumamit ka ng counta bakit di nalang ganto.


yung first simula mo sa 1000000000

the ang formula pababa is =if(a2="","",a1+1)

nahahabaan ako sa formula mo eh. and pag humaba ng ang data mo makonsumo sa memory na dahil per row ang bilang nya. lahat ng cell na may formula ng counta will work as that. count per cell. instead. plus 1 lang sa taas. ok na,

 

[bytesize]

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

sir kirby, tapos ka na sa assessment ? 2 days nalang.

- - - Updated - - -

- - - Updated - - -

mga sir, ask ko lang gusto ko sana 10 digit serial number sa column A mag display pag nag fill in sa column B. pano ba gawin to? salamat po!

you can also try this formula:

=IF(TRIM(B1)="","",SUM(--(TRIM(B$1:B1)<>""))+1000000000)

enter it as an array formula (control + shift + enter) then drag down.

see solution here: https://docs.google.com/spreadsheets/d/1NLpwAVb4a-yQHotjbDObaVO9Oa-LWaGLx_y_LWYmTbY/edit?usp=sharing

 

[bytesize]

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

sir kirby!! nakahabol ka ba sa submission?? #excelchampPH

 

[kirby21]

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

sir kirby!! nakahabol ka ba sa submission?? #excelchampPH

di na boss. nabulunan ako buong week. kakatapos lang kasi ng 3rd quarter. hanggang ngayon umay na umay pa ko sa numero at computation. hahaha. minsan may time talaga na, tinatamad ako maganalyze kahit minsan ang problema lang maliit lang. hahahaha. ano ba name mo? like ko nalang. para dagdag score din.