Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Smart calculations with Excel?

I would like to find out,if it is possilbe to create a fomulate that will do :

I enter project names in a row (day name) that has a corresponding column (a
15min timeslot)

11:00 11:15 11:30 11:45 12:00 ....
Monday Alpha Beta Omega Alpha Delta
Tuesday Beta Beta Alpha ....
...

I would like to have the project displayed (in a separate result cell) that
occurs the most, hower I would like not to have a numerical value as a result
- I would like to have the porject name displayed. Furthermore in an another
result cell I would like to have the time calculated that I spent working on
that project (my 15 min.time slots).

Is it possible to design such a function without doing a UDF?
Kind Regards for all suggestions or recommendations.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,510
Default Smart calculations with Excel?

Hi Christian,

Is the list of project names always drawn from a common list or are they
totally random names?

If they are drawn from a common list that can exist in the workbook
(preferrably on another worksheet) then your request is not too difficult to
achieve using the worksheet functions.

Regards,

OssieMac



"Christian" wrote:

I would like to find out,if it is possilbe to create a fomulate that will do :

I enter project names in a row (day name) that has a corresponding column (a
15min timeslot)

11:00 11:15 11:30 11:45 12:00 ....
Monday Alpha Beta Omega Alpha Delta
Tuesday Beta Beta Alpha ....
...

I would like to have the project displayed (in a separate result cell) that
occurs the most, hower I would like not to have a numerical value as a result
- I would like to have the porject name displayed. Furthermore in an another
result cell I would like to have the time calculated that I spent working on
that project (my 15 min.time slots).

Is it possible to design such a function without doing a UDF?
Kind Regards for all suggestions or recommendations.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Smart calculations with Excel?

Hi OssieMac,

thanks for the quick reply.
The project names are generated randomly(by the project managers and I work
on them until my share of work is done), but I could create a list of names
on the same sheet (or a different one) and keep it updated if new projects
would be added to my work portfolio.
What is the next step?
Kind Regards

Christian

"OssieMac" wrote:

Hi Christian,

Is the list of project names always drawn from a common list or are they
totally random names?

If they are drawn from a common list that can exist in the workbook
(preferrably on another worksheet) then your request is not too difficult to
achieve using the worksheet functions.

Regards,

OssieMac



"Christian" wrote:

I would like to find out,if it is possilbe to create a fomulate that will do :

I enter project names in a row (day name) that has a corresponding column (a
15min timeslot)

11:00 11:15 11:30 11:45 12:00 ....
Monday Alpha Beta Omega Alpha Delta
Tuesday Beta Beta Alpha ....
...

I would like to have the project displayed (in a separate result cell) that
occurs the most, hower I would like not to have a numerical value as a result
- I would like to have the porject name displayed. Furthermore in an another
result cell I would like to have the time calculated that I spent working on
that project (my 15 min.time slots).

Is it possible to design such a function without doing a UDF?
Kind Regards for all suggestions or recommendations.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,510
Default Smart calculations with Excel?

Hi Christian,

Firstly, the formulas are only going to find one MAX. If you have more than
one with the MAX value then I dont know how to handle that. However, you
will be able to view them on Sheet2 on the lookup table.

I really don't know how much help you need so I have gone in for the
overkill and hopefully you can sort it out.

Assume Sheet1 for your day of week and time of day data as per example you
posted.

The following is on Sheet2:-

Unique list of project names in Column B of Sheet2. Using your example
Project Names is the column header and the projects are in cells B2:B5.
(Column A blank at this stage)

Column B
Project Names
Alpha
Beta
Omega
Delta

In Cell A2 formula:-
=COUNTIF(Sheet1!$B$2:$F$4,B2)
Where Sheet1!$B$2:$F$4 is the range in Sheet1 containing all of your
projects under day of week and time of day. B2 is the first project name
(Alpha) in the unique list of project names in column B of on Sheet2.

Drag this formula to the bottom of the project names and in column A you
will have a list of numbers representing the count of each project name.
Should now look like this with numbers in column A and projects in column B:-

3 Alpha
3 Beta
1 Omega
1 Delta

In a blank cell under the count of names insert this formula:-

=MAX($A$2:$A$5)
Where $A$2:$A$5 is the list of numbers representing the count of each
project name. It returns the highest number. Should now look like this:-

3 Alpha
3 Beta
1 Omega
1 Delta
3

The following is on Sheet1:-

In any blank cell enter the following formula:-

=VLOOKUP(Sheet2!$A$7,Sheet2!$A$2:$B$5,2,FALSE)

Where Sheet2!$A$7 is the cell reference with the MAX formula.
Sheet2!$A$2:$B$5 is the range (2 columns) with the counts of projects plus
the project names.
2 represents the value in the second column to be displayed.
False means exact match only (not nearest match)

Basically the formula looks up the project name with the highest count.

In any other blank cell on Sheet1 for total time on max count project:-

Custom Format the cell to [h]:mm (The square brackets around the h
prevents Excel from displaying the time of the next day if the number of
hours exceed 24. Example 30:00 would show as 6:00 because Excel thinks it is
6:00 am the next day.)

Insert the following formula for the total time spent on the project with
the max count:-

=Sheet2!$A$7*TIMEVALUE("0:15")

Where Sheet2!$A$7 contains the MAX formula. TIMEVALUE("0:15") simply
converts the time entered as text into a numeric value. (You can enter the
time in another cell and simply insert the cell reference in place of "0:15".)

The VLOOKUP formula and the one to calculate the total time can actually
have the MAX formula nested in lieu of placing the max formula in a separate
cell and referencing the cell. Examples as follows:-

=VLOOKUP(MAX(Sheet2!$A$2:$A$5),Sheet2!$A$2:$B$5,2, FALSE)

Calculate the total time on max count project:-

=MAX(Sheet2!$A$2:$A$5)*TIMEVALUE("0:15")

Hope you have understood all this so far because now I am going to explain
how to make it even better.

You indicated that the number of project references could grow. Therefore, I
suggest that you name the ranges in your lookup sheet (Sheet2) and use the
named ranges in your formulas so that when the ranges alter in size all you
have to do is rename the ranges and the formulas will still be correct.

There are 2 ranges to name.

First range:-
Occurrences This will apply to Sheet2!$A$2:$A$5 (only one column)

Project_Reference This will apply to Sheet2!$A$2:$B$5 (2 columns)

The formulas will now look like this where the MAX is nested into the
formula:-

=VLOOKUP(MAX(Occurrences),Project_Reference,2,FALS E)

=MAX(Occurrences)*TIMEVALUE("0:15")

Feel free to get back to me if you have any further questions.

Regards,

OssieMac

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
mappoint smart tags in excel - (not working) Nick Gibbs Excel Discussion (Misc queries) 0 August 20th 07 11:18 AM
Problems between Hyperion Smart View Add-in and Excel Wes Excel Discussion (Misc queries) 0 May 31st 07 04:54 PM
How do I add a stock symbol smart tag in Excel? Teacher in Need Excel Discussion (Misc queries) 0 August 29th 06 10:59 PM
smart tags in Excel? Jr Geek Squad Wanna Be Excel Discussion (Misc queries) 0 September 21st 05 10:13 PM
Smart Tag in Excel does not recognize some stock symbols Chambersteacher Excel Discussion (Misc queries) 0 January 21st 05 12:13 AM


All times are GMT +1. The time now is 09:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"