if statement with functions

how can I get and if statement to read if(a value in cell d6 > 4, then
look-up a value in this table and return the amount hat value equals, else
entry is not greater than 4 and needs to be added to some sort of counter
that would keep track of instances from that id) any ideas? Thanks Beth

"Beth" <Beth@discussions.microsoft.com> wrote in message
news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> how can I get and if statement to read if(a value in cell d6 > 4, then
> look-up a value in this table and return the amount hat value equals, else
> entry is not greater than 4 and needs to be added to some sort of counter
> that would keep track of instances from that id) any ideas? Thanks Beth

I think you need to submit a complete example. What is "this table" and
"instances from that id" ?
/Fredrik

This is the set-up (military time):

12:00 13:36 96 <- this calculation is the amount of minutes that the time
frame creates this is the calculation to get the amount of minutes:
=MINUTE(B6)-MINUTE(C6)*(-1)+((HOUR(B6)-HOUR(C6))*(-1))*60 (Next to this cell
will be the amount of units that the minutes equals see below for ex.)

(will start line with)-> client # 12:00 13:36 96 7 <- 96
minutes = 7 units

so on another sheet there's a table that has ranges the first number is the
number of units the range is the amount of minutes that satisfy that unit
amount ex:
1 5 6 7 8 9 10 11 12 13 14 15 <- range 5-15 = 1 unit
2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <- range 16-30 = 2 units etc

this table goes all the way to 75 units so this table is large.

So what I am driving at is this:
If the # of minutes is > 4, then look up the minute amount in the table and
return the unit, else the number of minutes is less than 4 return 0)
Also at the end of the entries I want to tally up the amount of units that
each client # has because usually there are more than one entry per client.
Ex.

1000 13:00 13:04 4 0
1000 14:05 14:10 5 0 1 <- 1 unit because of two
entries...

Any suggestions or ideas this is a big one. I have been toying with it but
need some sort of guidance. Don't know VB or Excel to this extent. Thanks for
any suggestions or help that is offered it is greatly appreciated.






"Fredrik Wahlgren" wrote:

>
> "Beth" <Beth@discussions.microsoft.com> wrote in message
> news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> > how can I get and if statement to read if(a value in cell d6 > 4, then
> > look-up a value in this table and return the amount hat value equals, else
> > entry is not greater than 4 and needs to be added to some sort of counter
> > that would keep track of instances from that id) any ideas? Thanks Beth
>
> I think you need to submit a complete example. What is "this table" and
> "instances from that id" ?
> /Fredrik
>
>
>

If D2 contains your minute as a whole number, then in E2
=if(D2<4,0,Roundup(D2/15,30))

would give you the number units without having to have a large table.

Drag fill this formula down column E

Use the Sumif Function

=Sumif(A:A,"Client1",D:D)

--
Regards,
Tom Ogilvy



"Beth" <Beth@discussions.microsoft.com> wrote in message
news:91810C27-21BC-4A28-8A60-E46A0D3BD4B5@microsoft.com...
> This is the set-up (military time):
>
> 12:00 13:36 96 <- this calculation is the amount of minutes that the time
> frame creates this is the calculation to get the amount of minutes:
> =MINUTE(B6)-MINUTE(C6)*(-1)+((HOUR(B6)-HOUR(C6))*(-1))*60 (Next to this
cell
> will be the amount of units that the minutes equals see below for ex.)
>
> (will start line with)-> client # 12:00 13:36 96 7 <- 96
> minutes = 7 units
>
> so on another sheet there's a table that has ranges the first number is
the
> number of units the range is the amount of minutes that satisfy that unit
> amount ex:
> 1 5 6 7 8 9 10 11 12 13 14 15 <- range 5-15 = 1 unit
> 2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <- range 16-30 = 2 units
etc
>
> this table goes all the way to 75 units so this table is large.
>
> So what I am driving at is this:
> If the # of minutes is > 4, then look up the minute amount in the table
and
> return the unit, else the number of minutes is less than 4 return 0)
> Also at the end of the entries I want to tally up the amount of units that
> each client # has because usually there are more than one entry per
client.
> Ex.
>
> 1000 13:00 13:04 4 0
> 1000 14:05 14:10 5 0 1 <- 1 unit because of two
> entries...
>
> Any suggestions or ideas this is a big one. I have been toying with it but
> need some sort of guidance. Don't know VB or Excel to this extent. Thanks
for
> any suggestions or help that is offered it is greatly appreciated.
>
>
>
>
>
>
> "Fredrik Wahlgren" wrote:
>
> >
> > "Beth" <Beth@discussions.microsoft.com> wrote in message
> > news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> > > how can I get and if statement to read if(a value in cell d6 > 4, then
> > > look-up a value in this table and return the amount hat value equals,
else
> > > entry is not greater than 4 and needs to be added to some sort of
counter
> > > that would keep track of instances from that id) any ideas? Thanks
Beth
> >
> > I think you need to submit a complete example. What is "this table" and
> > "instances from that id" ?
> > /Fredrik
> >
> >
> >

sumif formula should have refered to E rather than D

=Sumif(A:A,"Client1",E:E)
--
Regards,
Tom Ogilvy


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:u12lz43OFHA.2144@TK2MSFTNGP09.phx.gbl...
> If D2 contains your minute as a whole number, then in E2
> =if(D2<4,0,Roundup(D2/15,30))
>
> would give you the number units without having to have a large table.
>
> Drag fill this formula down column E
>
> Use the Sumif Function
>
> =Sumif(A:A,"Client1",D:D)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Beth" <Beth@discussions.microsoft.com> wrote in message
> news:91810C27-21BC-4A28-8A60-E46A0D3BD4B5@microsoft.com...
> > This is the set-up (military time):
> >
> > 12:00 13:36 96 <- this calculation is the amount of minutes that the
time
> > frame creates this is the calculation to get the amount of minutes:
> > =MINUTE(B6)-MINUTE(C6)*(-1)+((HOUR(B6)-HOUR(C6))*(-1))*60 (Next to this
> cell
> > will be the amount of units that the minutes equals see below for ex.)
> >
> > (will start line with)-> client # 12:00 13:36 96 7 <- 96
> > minutes = 7 units
> >
> > so on another sheet there's a table that has ranges the first number is
> the
> > number of units the range is the amount of minutes that satisfy that
unit
> > amount ex:
> > 1 5 6 7 8 9 10 11 12 13 14 15 <- range 5-15 = 1 unit
> > 2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <- range 16-30 = 2 units
> etc
> >
> > this table goes all the way to 75 units so this table is large.
> >
> > So what I am driving at is this:
> > If the # of minutes is > 4, then look up the minute amount in the table
> and
> > return the unit, else the number of minutes is less than 4 return 0)
> > Also at the end of the entries I want to tally up the amount of units
that
> > each client # has because usually there are more than one entry per
> client.
> > Ex.
> >
> > 1000 13:00 13:04 4 0
> > 1000 14:05 14:10 5 0 1 <- 1 unit because of two
> > entries...
> >
> > Any suggestions or ideas this is a big one. I have been toying with it
but
> > need some sort of guidance. Don't know VB or Excel to this extent.
Thanks
> for
> > any suggestions or help that is offered it is greatly appreciated.
> >
> >
> >
> >
> >
> >
> > "Fredrik Wahlgren" wrote:
> >
> > >
> > > "Beth" <Beth@discussions.microsoft.com> wrote in message
> > > news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> > > > how can I get and if statement to read if(a value in cell d6 > 4,
then
> > > > look-up a value in this table and return the amount hat value
equals,
> else
> > > > entry is not greater than 4 and needs to be added to some sort of
> counter
> > > > that would keep track of instances from that id) any ideas? Thanks
> Beth
> > >
> > > I think you need to submit a complete example. What is "this table"
and
> > > "instances from that id" ?
> > > /Fredrik
> > >
> > >
> > >
>
>

great advice after slight tweaking ti works grand now for another question:

how can get the repeating clients w/ less than 4 unit to be tallied and then
added only once instead of for each row?

"Tom Ogilvy" wrote:

> If D2 contains your minute as a whole number, then in E2
> =if(D2<4,0,Roundup(D2/15,30))
>
> would give you the number units without having to have a large table.
>
> Drag fill this formula down column E
>
> Use the Sumif Function
>
> =Sumif(A:A,"Client1",D:D)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Beth" <Beth@discussions.microsoft.com> wrote in message
> news:91810C27-21BC-4A28-8A60-E46A0D3BD4B5@microsoft.com...
> > This is the set-up (military time):
> >
> > 12:00 13:36 96 <- this calculation is the amount of minutes that the time
> > frame creates this is the calculation to get the amount of minutes:
> > =MINUTE(B6)-MINUTE(C6)*(-1)+((HOUR(B6)-HOUR(C6))*(-1))*60 (Next to this
> cell
> > will be the amount of units that the minutes equals see below for ex.)
> >
> > (will start line with)-> client # 12:00 13:36 96 7 <- 96
> > minutes = 7 units
> >
> > so on another sheet there's a table that has ranges the first number is
> the
> > number of units the range is the amount of minutes that satisfy that unit
> > amount ex:
> > 1 5 6 7 8 9 10 11 12 13 14 15 <- range 5-15 = 1 unit
> > 2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <- range 16-30 = 2 units
> etc
> >
> > this table goes all the way to 75 units so this table is large.
> >
> > So what I am driving at is this:
> > If the # of minutes is > 4, then look up the minute amount in the table
> and
> > return the unit, else the number of minutes is less than 4 return 0)
> > Also at the end of the entries I want to tally up the amount of units that
> > each client # has because usually there are more than one entry per
> client.
> > Ex.
> >
> > 1000 13:00 13:04 4 0
> > 1000 14:05 14:10 5 0 1 <- 1 unit because of two
> > entries...
> >
> > Any suggestions or ideas this is a big one. I have been toying with it but
> > need some sort of guidance. Don't know VB or Excel to this extent. Thanks
> for
> > any suggestions or help that is offered it is greatly appreciated.
> >
> >
> >
> >
> >
> >
> > "Fredrik Wahlgren" wrote:
> >
> > >
> > > "Beth" <Beth@discussions.microsoft.com> wrote in message
> > > news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> > > > how can I get and if statement to read if(a value in cell d6 > 4, then
> > > > look-up a value in this table and return the amount hat value equals,
> else
> > > > entry is not greater than 4 and needs to be added to some sort of
> counter
> > > > that would keep track of instances from that id) any ideas? Thanks
> Beth
> > >
> > > I think you need to submit a complete example. What is "this table" and
> > > "instances from that id" ?
> > > /Fredrik
> > >
> > >
> > >
>
>
>

What are you tallying if the clients are less than 4. The units returned
should be 0. Are you tallying the minutes?

Do you want a table like

Client1 total minutes for 1
Client2 total minutes for 2

where the list would only be those clients that have at least one row that
is less than 4

anyway, it is unclear what you mean by tallied and then added only once.
(added to what. What's tallied, if you tallying by client, the what
difference does it make if you tally all the rows and add the sum once or
tally in each row in the final sum.

Bottom line, is I don't know what you are trying to do.

--
Regards,
Tom Ogilvy

"Beth" <Beth@discussions.microsoft.com> wrote in message
news:A61B8244-A2AE-4DEC-B009-724DD0C01C4D@microsoft.com...
> great advice after slight tweaking ti works grand now for another
question:
>
> how can get the repeating clients w/ less than 4 unit to be tallied and
then
> added only once instead of for each row?
>
> "Tom Ogilvy" wrote:
>
> > If D2 contains your minute as a whole number, then in E2
> > =if(D2<4,0,Roundup(D2/15,30))
> >
> > would give you the number units without having to have a large table.
> >
> > Drag fill this formula down column E
> >
> > Use the Sumif Function
> >
> > =Sumif(A:A,"Client1",D:D)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Beth" <Beth@discussions.microsoft.com> wrote in message
> > news:91810C27-21BC-4A28-8A60-E46A0D3BD4B5@microsoft.com...
> > > This is the set-up (military time):
> > >
> > > 12:00 13:36 96 <- this calculation is the amount of minutes that the
time
> > > frame creates this is the calculation to get the amount of minutes:
> > > =MINUTE(B6)-MINUTE(C6)*(-1)+((HOUR(B6)-HOUR(C6))*(-1))*60 (Next to
this
> > cell
> > > will be the amount of units that the minutes equals see below for ex.)
> > >
> > > (will start line with)-> client # 12:00 13:36 96 7 <-
96
> > > minutes = 7 units
> > >
> > > so on another sheet there's a table that has ranges the first number
is
> > the
> > > number of units the range is the amount of minutes that satisfy that
unit
> > > amount ex:
> > > 1 5 6 7 8 9 10 11 12 13 14 15 <- range 5-15 = 1 unit
> > > 2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <- range 16-30 = 2
units
> > etc
> > >
> > > this table goes all the way to 75 units so this table is large.
> > >
> > > So what I am driving at is this:
> > > If the # of minutes is > 4, then look up the minute amount in the
table
> > and
> > > return the unit, else the number of minutes is less than 4 return 0)
> > > Also at the end of the entries I want to tally up the amount of units
that
> > > each client # has because usually there are more than one entry per
> > client.
> > > Ex.
> > >
> > > 1000 13:00 13:04 4 0
> > > 1000 14:05 14:10 5 0 1 <- 1 unit because of two
> > > entries...
> > >
> > > Any suggestions or ideas this is a big one. I have been toying with it
but
> > > need some sort of guidance. Don't know VB or Excel to this extent.
Thanks
> > for
> > > any suggestions or help that is offered it is greatly appreciated.
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Fredrik Wahlgren" wrote:
> > >
> > > >
> > > > "Beth" <Beth@discussions.microsoft.com> wrote in message
> > > > news:3DE8EF29-8EF5-43A0-B88C-D000399DB7E6@microsoft.com...
> > > > > how can I get and if statement to read if(a value in cell d6 > 4,
then
> > > > > look-up a value in this table and return the amount hat value
equals,
> > else
> > > > > entry is not greater than 4 and needs to be added to some sort of
> > counter
> > > > > that would keep track of instances from that id) any ideas? Thanks
> > Beth
> > > >
> > > > I think you need to submit a complete example. What is "this table"
and
> > > > "instances from that id" ?
> > > > /Fredrik
> > > >
> > > >
> > > >
> >
> >
> >