Maximum Number of times

J

Jay

leo can this be done ! sorry if i m disturbing u cos i m novice in
excel :(

i appreciate it if you could solve this problem !
i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula or Module for
this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
...
..
..
..
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
..
..
..
28/02/2006 $1500.
..
..
31/12/2006 $1800


Sheet 2:

January ?
February
March
..
..
..
December


thanx in advance
regards
Jay
 
L

Leo Heuser

Jay said:
leo can this be done ! sorry if i m disturbing u cos i m novice in
excel :(

i appreciate it if you could solve this problem !
i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula or Module for
this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
..
.
.
.
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
.
.
.
28/02/2006 $1500.
.
.
31/12/2006 $1800


Sheet 2:

January ?
February
March
.
.
.
December


thanx in advance
regards
Jay
Click to expand...


Last gasp!

For January:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=1)*(Sheet1!$B$2:$B$100))

For February:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=2)*(Sheet1!$B$2:$B$100))

etc.

Assuming all data from same year.

Regards
Leo Heuser
 
J

Jay

thanx leo,

i m getting #VALUE error in the maximum lost % , i deleted other
worksheet its giving this error now ? what to do now

thanx for last problems solution

regards
Jay

Last One : how do i count the number of times B occured in a particular
month ?

A
A
B
B
B
 
L

Leo Heuser

Jay said:
thanx leo,
Click to expand...


You're welcome, Jay.
i m getting #VALUE error in the maximum lost % , i deleted other
worksheet its giving this error now ? what to do now
Click to expand...

There was an error in my function. It was not possible to
use data on a different sheet from the one, where the formula
resided. This has been rectified below. Sorry about that.
Please see, if this was, what caused the #VALUE error.


Function MaxBank(BankRange As Range) As Double
'Leo Heuser, Sep. 18, 2006, ver. 1.01
Dim BankRangeValue As Variant
Dim Counter As Long
Dim Counter1 As Long
Dim CountElement As Long
Dim GetMaxiValue As Double
Dim GetMiniValue As Double
Dim MaxiBankRow() As Long
Dim MiniBankValue() As Double
Dim Result() As Double
Dim SheetName As String

Set BankRange = BankRange.Columns(1)
SheetName = BankRange.Parent.Name

If IsEmpty(BankRange.Cells(1, 1). _
Offset(BankRange.Rows.Count)) Then
Set BankRange = Range(BankRange.Cells(1, 1), _
Sheets(SheetName).Cells(ActiveSheet.Rows.Count, _
BankRange.Column).End(xlUp))
End If

If BankRange.Rows.Count = 1 Then
MaxBank = 0
GoTo Finito
End If

BankRangeValue = BankRange.Value

GetMaxiValue = BankRangeValue(1, 1)

ReDim MaxiBankRow(1 To UBound(BankRangeValue, 1))

CountElement = 1

MaxiBankRow(CountElement) = CountElement

For Counter = 2 To UBound(BankRangeValue, 1) - 1
If BankRangeValue(Counter, 1) > GetMaxiValue Then
If BankRangeValue(Counter, 1) > _
BankRangeValue(Counter + 1, 1) Then
CountElement = CountElement + 1
GetMaxiValue = BankRangeValue(Counter, 1)
MaxiBankRow(CountElement) = Counter
End If
End If
Next Counter

MaxiBankRow(CountElement + 1) = Counter

ReDim Preserve MaxiBankRow(1 To CountElement + 1)
ReDim MiniBankValue(1 To UBound(MaxiBankRow))
ReDim Result(1 To UBound(MaxiBankRow))

For Counter = 1 To UBound(MiniBankValue) - 1
GetMiniValue = BankRangeValue(MaxiBankRow(Counter), 1)

For Counter1 = MaxiBankRow(Counter) To MaxiBankRow(Counter + 1)
If BankRangeValue(Counter1, 1) < GetMiniValue Then
GetMiniValue = BankRangeValue(Counter1, 1)
End If
Next Counter1

MiniBankValue(Counter) = GetMiniValue
Next Counter

MiniBankValue(Counter) = _
BankRangeValue(UBound(BankRangeValue, 1), 1)

For Counter = 1 To UBound(Result)
Result(Counter) = (BankRangeValue(MaxiBankRow(Counter), 1) - _
MiniBankValue(Counter)) / _
BankRangeValue(MaxiBankRow(Counter), 1)
Next Counter

MaxBank = Application.Max(Result)

Finito:

End Function


Last One : how do i count the number of times B occured in a particular
month ?

A
A
B
B
B
Click to expand...

If dates in A2:A100 and Bs in C2:C100

For January:
=SUMPRODUCT((MONTH(A2:A100)=1)*(C2:C100="b"))

For March:
=SUMPRODUCT((MONTH(A2:A100)=3)*(C2:C100="b"))


Regards
Leo Heuser
 
L

Leo Heuser

Jay said:
leo thanx,
Click to expand...

Does that mean, that the new version of "MaxBank" took care of
the error?

i m gettting this error on counting B

#NA erro

any clue ?
Click to expand...

Since you haven't told me how things are organized,
I haven't got a clue. Where are the dates and where
are the Bs? Is the formula in the same sheet as the lists?
How does your formula look?

Regards
Leo Heuser
 
J

Jay

leo
Thanx for all your help,

but i encountered a problem with the max % lost when i put a value on
withdrawel column as bank will reduce and this gave way to max % lost
to more than 99%

how do i rectify this problem

I have a withdrawel and deposit column when ever i have put in or out
money Bank column changes according to if deposit it will increase ,
withdrawel then bank will reduce i dont want this deposit and
withdrawel should effect the max % lost ?

how do i rectify this

regarrds
 
L

Leo Heuser

Jay said:
leo
Thanx for all your help,

but i encountered a problem with the max % lost when i put a value on
withdrawel column as bank will reduce and this gave way to max % lost
to more than 99%

how do i rectify this problem

I have a withdrawel and deposit column when ever i have put in or out
money Bank column changes according to if deposit it will increase ,
withdrawel then bank will reduce i dont want this deposit and
withdrawel should effect the max % lost ?

how do i rectify this

regarrds
Jay
Click to expand...



Jay

You can attach a copy of your workbook to a personal mail,
and I'll take a look at it. Which version of Excel are you using?

Regards
Leo Heuser
 
L

Leo Heuser

Jay said:
hi

which address should i send the excel ?

Regards
Jay
Click to expand...

Hi

I mentioned that in my posting from 13 Sep.

leo.heuser at adslhome.dk

Regards
Leo Heuser
 
L

Leo Heuser

Jay said:
Leo,

i m looking forward for your reply

regards
Jay
Click to expand...

Jay

I have used a lot of time to help you, but when you
add new information on the fly, and it turns out, that
it's not just a snap to include it in the code, I am not
inclined to continue using time on the function.

The next time you ask for help in the groups, please
disclose *all* information from the start.

Nobody gets paid for helping in the groups. We do it,
because we like helping and because it's fun, but
it's not fun having spent an hour or two to help you, only
to be told, that I wasted my time because you didn't
supply all the necessary pieces of information from the
beginning.

Regards
Leo Heuser
 
J

Jay

Leo

Thank you for all your help

Regards
Jay
Leo said:
Jay

I have used a lot of time to help you, but when you
add new information on the fly, and it turns out, that
it's not just a snap to include it in the code, I am not
inclined to continue using time on the function.

The next time you ask for help in the groups, please
disclose *all* information from the start.

Nobody gets paid for helping in the groups. We do it,
because we like helping and because it's fun, but
it's not fun having spent an hour or two to help you, only
to be told, that I wasted my time because you didn't
supply all the necessary pieces of information from the
beginning.

Regards
Leo Heuser
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto format sheet 1
Excel How to find the most common pairs and triplets numbers? 5
How to count the maximum number flanked by zeros 9
Modify working code to expand the output. 5
Marking found records in Workbook A from Target List in Workbook B 1
Comparing numbers 1
How do I count the number of times a particular word is used? 2
How to fix Steam game icons appearing as .url files 1

Top