Welcome to the North American Subaru Impreza Owners Club Friday March 29, 2024
Home Forums Images WikiNASIOC Products Store Modifications Upgrade Garage
NASIOC
Go Back   NASIOC > NASIOC Miscellaneous > Off-Topic

Welcome to NASIOC - The world's largest online community for Subaru enthusiasts!
Welcome to the NASIOC.com Subaru forum.

You are currently viewing our forum as a guest, which gives you limited access to view most discussions and access our other features. By joining our community, free of charge, you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is free, fast and simple, so please join our community today!

If you have any problems with the registration process or your account login, please contact us.







* As an Amazon Associate I earn from qualifying purchases. 
* Registered users of the site do not see these ads. 
Reply
 
Thread Tools Display Modes
Old 01-04-2008, 09:37 AM   #1
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Question Anyone with a kind heart want to help with Excel?

http://www.mediafire.com/?1do5smnxizv
^ Spreadsheet example.




Hello everyone:

I was hoping that someone would be able to help me on a pretty decent size project that I am working on. I am hoping that I will be able to do everything with excel functions, and not have to use a macro, however I am not sure how possible that will be. Although it looks like a lot, I fell like it should be pretty straight forward for anyone who is pretty decent at this kind of stuff.

Here is what I have:
First of all, I am provided with a number of lines of data (weather data to be precise)

75010101000000000000?00000?00000?00000?00000?00000 ?00000?005B803B8-080B8-125E7067B80788B8060B8057B89999?099999?009999999990 05F8020F8008A700E7

Each line look like this except with varying numbers. This is the raw weather data that is provided to us, and each position consists of Month, date, hour, dry bulb, etc***8230;

For each city there are about 8760 of these lines and they are broken down like such Month 1, Day 1, Hour 1 - Month 1, Day 1, Hour 2 - - - Month 12, Day 31, Hour 12.

Now the spread sheet that I am attempting to make will allow the user to choose the days of the week, and hours during the selected days. I then need to somehow pull out the wet bulb and dry bulb temperatures and display the number of hours during the month that the temperature is between a certain range.

For example, I want to see the results for Weekdays (M-F) during the hours of 6AM to 6PM***8230;For a more comprehensive layout one can examine the attached spreadsheet. (Note, The numbers in the spreadsheet were manually entered from a random city's data and is simply meant to display the finished product)

In the attached sample for example, we can see that during the month of January the temperature was in the range of 40-45 for a total of 79 hours.

On the next page you can see an example of the weather data string. The location of important data is as follows:
Possition Information
4-5 Month
6-7 Day (not really needed)
8-9 Hour
68-71 Dry Bulb temperature.

As a note, there is a space before each line which counts as a character position.
And if at all possible, I would love to be able to keep the adjustable temperature scale as seen on page 1.

Hopefully someone will be able to help me out with this, as I am not the most skilled person when it comes to Excel Functions and what not.

Thanks a bunch everyone.
* Registered users of the site do not see these ads.
Spitfire999 is offline   Reply With Quote
Sponsored Links
* Registered users of the site do not see these ads.
Old 01-04-2008, 09:41 AM   #2
Asinine
Sufficient for Sharky
 
Member#: 55173
Join Date: Feb 2004
Chapter/Region: MAIC
Location: I beat up big rocks
Vehicle:
with paddles.

Default

I'll help you, but you have to promise me undergrad p***y when I come up for Arts Fest in July.
Asinine is offline   Reply With Quote
Old 01-04-2008, 09:46 AM   #3
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Haha sure, and free booze...30 pack of Coors sound good? Maybe something a bit more upscale?
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 09:47 AM   #4
Asinine
Sufficient for Sharky
 
Member#: 55173
Join Date: Feb 2004
Chapter/Region: MAIC
Location: I beat up big rocks
Vehicle:
with paddles.

Default

Quote:
Originally Posted by Spitfire999 View Post
Haha sure, and free booze...30 pack of Coors sound good? Maybe something a bit more upscale?
You had me until "Coors." You have unfettered access to cheap Yuengling, and you offer me Coors? Do your own homework. I'm out!
Asinine is offline   Reply With Quote
Old 01-04-2008, 09:47 AM   #5
sonicblue
Scooby Specialist
 
Member#: 25227
Join Date: Sep 2002
Chapter/Region: Tri-State
Location: Oh s**t I ain't from Brooklyn!
Vehicle:
2012 Escape
Black

Default

I can do anything in Excel......



..
.
.
.
.
. but I didn't understand a word of that.
sonicblue is offline   Reply With Quote
Old 01-04-2008, 09:51 AM   #6
Corn-Picker
Scooby Specialist
 
Member#: 8679
Join Date: Jul 2001
Location: Morgantown, WV
Vehicle:
2010 The most hated
vehicle on the internets

Default

Quote:
Originally Posted by Spitfire999 View Post

Here is what I have:
First of all, I am provided with a number of lines of data (weather data to be precise)

75010101000000000000?00000?00000?00000?00000?00000 ?00000?005B803B8-080B8-125E7067B80788B8060B8057B89999?099999?009999999990 05F8020F8008A700E7

Each line look like this except with varying numbers. This is the raw weather data that is provided to us, and each position consists of Month, date, hour, dry bulb, etc…

For each city there are about 8760 of these lines and they are broken down like such Month 1, Day 1, Hour 1 – Month 1, Day 1, Hour 2 - - - Month 12, Day 31, Hour 12.

Now the spread sheet that I am attempting to make will allow the user to choose the days of the week, and hours during the selected days. I then need to somehow pull out the wet bulb and dry bulb temperatures and display the number of hours during the month that the temperature is between a certain range.

For example, I want to see the results for Weekdays (M-F) during the hours of 6AM to 6PM…For a more comprehensive layout one can examine the attached spreadsheet. (Note, The numbers in the spreadsheet were manually entered from a random city’s data and is simply meant to display the finished product)

In the attached sample for example, we can see that during the month of January the temperature was in the range of 40-45 for a total of 79 hours.

On the next page you can see an example of the weather data string. The location of important data is as follows:
Possition Information
4-5 Month
6-7 Day (not really needed)
8-9 Hour
68-71 Dry Bulb temperature.

As a note, there is a space before each line which counts as a character position.
And if at all possible, I would love to be able to keep the adjustable temperature scale as seen on page 1.

Hopefully someone will be able to help me out with this, as I am not the most skilled person when it comes to Excel Functions and what not.

Thanks a bunch everyone.


Does your data always occupy the same number of spaces? That is, would you represent five as 005 and ten as 010, or would they be represented as 5 and 10? If it's the former, you can use the MID function to extract the different parts of the data.

For counting the number of times the temperature is within a range you'll need a few IF statements and a few COUNTIF statements.
Corn-Picker is offline   Reply With Quote
Old 01-04-2008, 09:54 AM   #7
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Quote:
Originally Posted by Asinine View Post
You had me until "Coors." You have unfettered access to cheap Yuengling, and you offer me Coors? Do your own homework. I'm out!
nooooo...well if I can change your mind by offering Yuengling I would be grateful...

Quote:
I can do anything in Excel......

. but I didn't understand a word of that.
Sorry, it's a little hard to explain in writing, if you open up the attached spreadsheet it might help you understand a bit more, if not, I suck at explaining stuff and will try explaining again.
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 09:57 AM   #8
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Quote:
Originally Posted by Corn-Picker View Post
Does your data always occupy the same number of spaces? That is, would you represent five as 005 and ten as 010, or would they be represented as 5 and 10? If it's the former, you can use the MID function to extract the different parts of the data.

For counting the number of times the temperature is within a range you'll need a few IF statements and a few COUNTIF statements.
It is actually the former. In my previous attempts, I have used the MID function to extract the data and the COUNTIF to count the data, (both parts which I got to work), however combining them together to put them in a table (as shown in the example) is proving difficult.
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 10:00 AM   #9
Asinine
Sufficient for Sharky
 
Member#: 55173
Join Date: Feb 2004
Chapter/Region: MAIC
Location: I beat up big rocks
Vehicle:
with paddles.

Default

There are a few ways to do it, but I can't tell with any certainty which is most efficient because I don't know the Excel API or internal functions.

In psuedocode:

1. Write a function to iterate through the cells and find the desired data (find cell where data at position XXX = cell XY--where the desired values have been entered into XY).
2. Assign a cell to the value of that function (remember, this is psuedocode, and in reality you'd actually choose the cell and write the function in there).
3. Repeat steps 1 and 2 to fill cells with desired data.
4. Manipulate data in those cells as desired.

OR

Replace #2 above with: Assign a cell and fill it with references to the cells that match.

This may not be very helpful, so the chick doesn't have to be all the hot and the Yuengling can be in cans.
Asinine is offline   Reply With Quote
Old 01-04-2008, 10:06 AM   #10
Corn-Picker
Scooby Specialist
 
Member#: 8679
Join Date: Jul 2001
Location: Morgantown, WV
Vehicle:
2010 The most hated
vehicle on the internets

Default

Quote:
Originally Posted by Asinine View Post

1. Write a function...
In Excel, when you write your own function, you use VB, which means you have created a "macro" enabled workbook.

Looks like you'll have to drink piss out of a Dixie cup
Corn-Picker is offline   Reply With Quote
Old 01-04-2008, 10:09 AM   #11
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Quote:
Originally Posted by Asinine View Post
There are a few ways to do it, but I can't tell with any certainty which is most efficient because I don't know the Excel API or internal functions.

In psuedocode:

1. Write a function to iterate through the cells and find the desired data (find cell where data at position XXX = cell XY--where the desired values have been entered into XY).
2. Assign a cell to the value of that function (remember, this is psuedocode, and in reality you'd actually choose the cell and write the function in there).
3. Repeat steps 1 and 2 to fill cells with desired data.
4. Manipulate data in those cells as desired.

OR

Replace #2 above with: Assign a cell and fill it with references to the cells that match.

This may not be very helpful, so the chick doesn't have to be all the hot and the Yuengling can be in cans.
I kinda have a pretty good idea how to do it conceptually, however it's getting the actual code/function down which is kinda confusing...I figure if i can get one cell down, the rest should be pretty much the same with slightly different numbers...

Don't worry your still get your brews...I wouldn't want to miss a chance to be verbally berated by a drunk Asinine in person as opposed to over the interwebs.
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 10:10 AM   #12
Asinine
Sufficient for Sharky
 
Member#: 55173
Join Date: Feb 2004
Chapter/Region: MAIC
Location: I beat up big rocks
Vehicle:
with paddles.

Default

What's it called when you use stuff like "=SUM(A3:A7)!" in a cell? Not called a function?
Asinine is offline   Reply With Quote
Old 01-04-2008, 10:13 AM   #13
sonicblue
Scooby Specialist
 
Member#: 25227
Join Date: Sep 2002
Chapter/Region: Tri-State
Location: Oh s**t I ain't from Brooklyn!
Vehicle:
2012 Escape
Black

Default

Quote:
Originally Posted by Spitfire999 View Post
Sorry, it's a little hard to explain in writing, if you open up the attached spreadsheet it might help you understand a bit more, if not, I suck at explaining stuff and will try explaining again.
I'm pretty sure I know how to do what you want, but I can't open the file. Email it to me, with another attempt at description, and I'll take a look.

tighe15 at gmail dot com
sonicblue is offline   Reply With Quote
Old 01-04-2008, 10:13 AM   #14
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Quote:
Originally Posted by Corn-Picker View Post
In Excel, when you write your own function, you use VB, which means you have created a "macro" enabled workbook.

Looks like you'll have to drink piss out of a Dixie cup
I believe that there is a difference between a function and a macro...

With a function you don't need to use VBA, unlike with a macro.
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 10:15 AM   #15
sonicblue
Scooby Specialist
 
Member#: 25227
Join Date: Sep 2002
Chapter/Region: Tri-State
Location: Oh s**t I ain't from Brooklyn!
Vehicle:
2012 Escape
Black

Default

In Excel, you can create a CUSTOM function, and that uses vb. Standard Excel functions do not use vb.
sonicblue is offline   Reply With Quote
Old 01-04-2008, 10:16 AM   #16
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Quote:
Originally Posted by sonicblue View Post
I'm pretty sure I know how to do what you want, but I can't open the file. Email it to me, with another attempt at description, and I'll take a look.

tighe15 at gmail dot com
Thanks, I'm typing up a new description now.
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 10:31 AM   #17
Spitfire999
Scooby Newbie
 
Member#: 143011
Join Date: Mar 2007
Chapter/Region: MAIC
Location: Arlington - VA
Vehicle:
2012 128i M-sport
Black

Default

Quote:
Originally Posted by Spitfire999 View Post
Thanks, I'm typing up a new description now.
Sent.
Spitfire999 is offline   Reply With Quote
Old 01-04-2008, 10:47 AM   #18
Corn-Picker
Scooby Specialist
 
Member#: 8679
Join Date: Jul 2001
Location: Morgantown, WV
Vehicle:
2010 The most hated
vehicle on the internets

Default

Quote:
Originally Posted by Asinine View Post
What's it called when you use stuff like "=SUM(A3:A7)!" in a cell? Not called a function?
SUM is a function, and it doesn't use VB. However, if you want a custom function, that's when you need VB. The trick is getting Excel's functions to take the conditional actions you want and return the values you need without using any custom code. It's a fun challenge because it's less of a sand box than a traditional language. Psuedocode usually doesn't help a person because they already know the psuedocode -- it's the process of transforming that psuedocode into the analagous Excel functions that's the difficult part.
Corn-Picker is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Anyone NJ or near by want to help with a swap serg Tri-State Area Forum 20 08-16-2004 12:14 AM
Anyone near N. Mississippi want to help with 6 spd install???? eightballrj South East Region Forum 4 08-21-2003 02:54 PM
Anyone want to help with installs? blindclown New England Impreza Club Forum -- NESIC 19 08-12-2003 07:26 PM
Does anyone want to help with uppipe or spring install? Also need install suggestions rex n effect Mid Atlantic Impreza Club -- MAIC 4 10-03-2002 01:54 AM
I'm buying Bill Harvey's old brakes on Mon...anyone want to help with the install? Sean New England Impreza Club Forum -- NESIC 5 09-23-2001 03:36 PM

All times are GMT -4. The time now is 03:05 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Powered by Searchlight © 2024 Axivo Inc.
Copyright ©1999 - 2019, North American Subaru Impreza Owners Club, Inc.

As an Amazon Associate I earn from qualifying purchases.

When you click on links to various merchants on this site and make a purchase, this can result in this site earning a commission
Affiliate programs and affiliations include, but are not limited to, the eBay Partner Network.