Sabtu, 01 Februari 2014

[belajar-excel] Digest Number 2760

4 New Messages

Digest #2760

Messages

Fri Jan 31, 2014 2:21 pm (PST) . Posted by:

ekaharsanto

Dear all master.....
Tolong bantu saya sedang membuat tabel distribusi mengajar. Ada dosen yang mengajar beberapa mata kuliah, saat saya akan menghitung berapa jumlah beban sks masing-masing dosen, ternyata sulit.....
Mohon bantuannya
File-nya saya lampirkan.
Terima kasih sebelumnya.....

Fri Jan 31, 2014 4:11 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Eka,

File terlampir menggunakan sebuah kolom bantu di sisi data. Kolom bantu
tersebut menggunakan fungsi LookUp. Mungkin coretan yang ada
disini<http://excel-mr-kid.blogspot.com/2013/09/menyingkat-if-yang-puanjuaaaang-buanget.html>bisa
memberi gambaran tentang penggunaan lookup yang ada dalam file yang
dilampirkan.

Pada sisi output, daftar nama disusun dengan array formula unique list
karena berasumsi bahwa jumlah record data yang diolah tidak mencapai
puluhan ribu dan jumlah cacah nama unique juga tidak mencapai ribuan. Pada
data yang banyak (mencapai puluhan ribu lebih dan jumlah unique yang
mencapai ribuan lebih), array formula akan terasa berat untuk dikalkulasi
oleh Excel, sehingga Excel akan tampak bekerja dengan lambat. Coretan
tentang array formula ada
disini<http://excel-mr-kid.blogspot.com/2011/03/array-formula-kenalan-yuuuk.html>.
Sedangkan coretan tentang konsep penyusunan unique list dengan array
formula ada disini<http://excel-mr-kid.blogspot.com/2011/03/formula-penyusun-data-unique-dan.html>
.

Semoga sesuai harapan.

Wassalam,
Kid.

2014-01-31 <ekaharsanto@gmail.com>:

>
>
> Dear all master.....
> Tolong bantu saya sedang membuat tabel distribusi mengajar. Ada dosen yang
> mengajar beberapa mata kuliah, saat saya akan menghitung berapa jumlah
> beban sks masing-masing dosen, ternyata sulit.....
> Mohon bantuannya
> File-nya saya lampirkan.
> Terima kasih sebelumnya.....
>
>

Sat Feb 1, 2014 12:57 am (PST) . Posted by:

ekaharsanto

terimakasih atas kesediaannya membantu, mohon, maaf ketika saya coba copykan kok tidak bisa ya? mohon tambahan pencerahan.... karena saya masih newbie....

Sat Feb 1, 2014 1:33 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Eko,

Coba diceritakan proses copy nya bagaimana. Hasil copy yang gak sukses
tersebut bisa dilampirkan kembali.

Wassalam,
Kid.

2014-02-01 <ekaharsanto@gmail.com>:

>
>
> terimakasih atas kesediaannya membantu, mohon, maaf ketika saya coba
> copykan kok tidak bisa ya? mohon tambahan pencerahan.... karena saya masih
> newbie....
>
>
GROUP FOOTER MESSAGE
=====================================================================
Untuk memudahkan tim penyusun materi Belajar Excel yang lebih sesuai kebutuhan member, silakan ungkapkan permasalahan yang kerap ditemui dalam menggunakan Excel sehari-hari atau hal-hal yang ingin dipelajari dalam jangka dekat ini. Mohon diprioritaskan dari yang sering ditemui sampai yang ingin dipelajari.
Isi sesuai kelompoknya (fitur-fitur, formula-formula tertentu yang masih membingungkan, otomasi atau pemrograman dalam Excel [Macro - VBA], hal lainnya yang membuat Anda kesulitan dalam mempelajari Excel).
Boleh mengisi berulang kali untuk menambah uneg-uneg yang ingin diungkapkan.
Link untuk menuangkan seluruh uneg-uneg tersebut ada di :
http://tech.groups.yahoo.com/group/belajar-excel/database?method=addRecord&tbl=3
=====================================================================
Langkah kecil Anda dalam mengisi database bisa menjadi langkah pertama yang bermanfaat besar untuk kita semua.
=====================================================================

---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com

berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe):
kirim mail kosong ke  belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 04.09 0 komentar

[ExcelVBA] File - GroupInfo.txt

 


This automatic email is posted to this group each month for the benefit of new members and as a reminder to current members.

NOTICE VERSION: January, 2012

* This group is monitored by several moderators. In an effort to help weed out spammers, most posts are manually verified before they are released

to the group. Therefore, if you are new to the group or do not regularly participate, it may take a bit longer for your post to show up in the

group. This is normal. We do try to keep watch and release valid posts ASAP. Once you become a recognized/active member of the group, we try to

make sure your membership is validated so that your posts can slide through without monitoring delays.

* Although we do try to keep posts on topic, we also like to be a friendly, community group here, so the occasional chatter between members is not

a problem. If you have something important to post that is "off topic"...please start the subject with OT-, as in "Off Topic" so that it can be

filtered out by those not wishing to be bothered by off topic posts.

* PLAY NICE! Verbal bashing of any kind will NOT be tolerated! If you have a problem with someone in this group...take it outside. If a complaint

is reported about your behavior, you'll be banned from the group...NO questions asked!

* This group has a web site. To locate the URL to the group's web page, see the bottom of any post where additional information is listed. Within

the group's web site, you'll find additional information. Depending on the activity/participation of the group, you may find additional help files

and/or tutorials, as well as other helpful links.

* Sadly, due to spammers, only moderators can post files and links within our group's site. However, if you have something you'd like to

contribute, contact one of the moderators...whose names are listed on the home page of our group's web page.

* Additionally, you cannot attach files to posts...again due to spammers. But if you have a file that you'd like others to see in an attempt to

help you solve a problem, contact a moderator to post the file for you. If you have any trouble contacting a moderator, feel free to request help

by posting to the group with the subject NEED MODERATOR HELP. (By using that EXACT subject, moderators can set an alert to more quickly notice your

post.)

* To get the best help, fast, use a good subject line! Don't just post a subject saying HELP! Give a little detail about the type of help you need.

Also be sure to include some details, such as the content of any error messages, as well as the software version you are using. For more specific

info on how to get the most from your posts, see this link: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=507#info

* With the help of several wonderful moderators and terrific group participants who regularly share their knowledge, I run a handful of free

support groups. To find our other groups, as well as other groups I recommend for free support, see this link:

http://www.mousetrax.com/resources.html

* And finally, know that you can find many free tutorials linked from my TechPage here: http://www.mousetrax.com/techpage.html and also directly

through TechTrax, my very popular, free ezine (online magazine) here: http://www.techtrax.us

Dian D. Chapman (Group Owner)
Technical Consultant, Microsoft MVP
MOS Certified Instructor, Editor/TechTrax Ezine
Tech Editor for Word & Office 2007 Bibles
https://mvp.support.microsoft.com/profile/Dian.Chapman

Dian's Soldier/K9 Site
http://www.mousetrax.com/dian/angels.html

Free Computer Tutorials: http://www.techtrax.us
Dian's Free User Support Groups: http://www.mousetrax.com/resources.html
Learn VBA the easy way: http://www.mousetrax.com/techcourses.html

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (84)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 03.28 0 komentar

[smf_addin] Digest Number 2951

5 New Messages

Digest #2951

Messages

Fri Jan 31, 2014 7:28 pm (PST) . Posted by:

"Marc ." whichwaytobeach

Hello,
I am using the Google's Get Element #s: 3007, 3042, 3052, 3057, 3072, 3102, 3142, 3197, and 596. Most of the times, I do get numbers that come back. But, there are times when I get #VALUE! strung across all of them, but yet the data is there when you look at the Google Quarterly Balance Sheet. I am not talking Financials here. For instance, when I type in ANV, I get #VALUE!, yet, when you go to https://www.google.com/finance?q=AMEX:ANV&fstype=ii all of the data is there.
Why?
Thanks for any help.
Regards,
Marc

Fri Jan 31, 2014 7:33 pm (PST) . Posted by:

mikemcq802

Use AMEX:ANV as the ticker for the RchGetElementNumber function.

Google requires more explicit tickers often.

Fri Jan 31, 2014 7:38 pm (PST) . Posted by:

"Marc ." whichwaytobeach

Thank you, Mike. It works like a charm!
I appreciate your writing back quickly!
Marc

To: smf_addin@yahoogroups.com
From: mikemcq802@yahoo.com
Date: Fri, 31 Jan 2014 19:33:29 -0800
Subject: [smf_addin] RE: #VALUE! Even Though The Data IS There...

Use AMEX:ANV as the ticker for the RchGetElementNumber function.

Google requires more explicit tickers often.

Fri Jan 31, 2014 8:38 pm (PST) . Posted by:

adam_sommers

Right - the formatting shouldn't matter. In my referenced formula =smfGetYahooOptionQuote(D16,"P",DATE(YEAR($E$1),MONTH($E$1),DAY($E$1)),H16,"a")


Cell D16 is COH
Cell E1 is 3/21/2014
And where I've identified the problem, cell H16 is 45.00 (formatting is irrelevant).


If I reference cell H16 in the formula, I get an error. If I type in "OTM2" it works just fine. I'm still stymied, and hope I explained my problem more succinctly.


Adam


---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

How a cell is formatted should be irrelevant. And whether it's a cell reference or a number or a literal should be irrelevant. An EXCEL (or user-defined) function would only use the value of the parameter.

Without knowing your cell values, I have no details to look at.


I just tried an example with both the literal "OTM1" and a cell with the value of "OTM1". Both worked fine for
me. I even added a numeric format on the "OTM1" cell value.


But I will mentions that "OTMx" and "ITMx" are unreliable with Yahoo on web page where they combine multiple expiration dates. It can also be unreliable on equities with a small number of options, since the ITM/OTM decisions key off of the shading Yahoo uses. So if there is no change in shading, the function will fail.

I always use smfGetOptionStrikes() to determine my ITM and OTM strike prices.


I probably should obsolete the "ITMx" and "OTMx" designations. Or change them to use the smfGetOptionStrikes() function instead -- which would allow it to be done for all sources, but slow down data retrieval times.

On Fri, Jan 31, 2014 at 2:47 PM, <adam@... mailto:adam@...> wrote:

Thank you, Randy for the updated version. But now that I've installed it, my SMFGetYahooOptionQuote function is no longer working when I refer to a strike price in a cell that is formatted as number or currency. I can get it to work if I put "OTM1" in the formula, but if I put in a cell reference for the strike of a Put, it generates "Error".'


Here is my formula: =smfGetYahooOptionQuote(D16,"P",DATE(YEAR($E$1),MONTH($E$1),DAY($E$1)),H16,"a"


Do you know why this is happening, and can it be fixed?











Fri Jan 31, 2014 9:09 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

OK. The problem is the date. The March expiration date is 3/22/2014, not
3/21/2014. Options expire on Saturday, not Friday. Friday is just the last
date they can be traded.

The reason it works for "OTM2" is that such a parameter means the function
doesn't use the exact date provided. It just uses the month. That's because
there is only one change of shading on each monthly page, which is then
what triggers the lookup to find the strike price and retrieve its data.
Basically, "OTM2" says to get the strike price that is 2 rows above the
shading change.

BTW, you can get the March monthly expiration date with:

=smfGetOptionExpiry(2014,3,"M")

On Fri, Jan 31, 2014 at 9:38 PM, <adam@sommersfinancial.com> wrote:

> Right - the formatting shouldn't matter. In my referenced formula
> =smfGetYahooOptionQuote(D16,"P",DATE(YEAR($E$1),MONTH($E$1),DAY(
> $E$1)),H16,"a")
>
> Cell D16 is COH
>
> Cell E1 is 3/21/2014
>
> And where I've identified the problem, cell H16 is 45.00 (formatting is
> irrelevant).
>
> If I reference cell H16 in the formula, I get an error. If I type in
> "OTM2" it works just fine. I'm still stymied, and hope I explained my
> problem more succinctly.
>
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 00.43 0 komentar
Langganan: Postingan (Atom)