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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >
Sorry I was so unclear.
What I want to accomplish is this:
Client Begin End Minute Unit Total Unit
100 12:00 12:15 15 1 0
100 12:20 12:25 5 1 0
100 12:30 12:31 1 0 2
101 12:40 12:41 1 0 0
what I want i to get the total units to display once after all entries are
in for that client, so if there is one entry then there will be a unit amount
and a total amount that match, if there's more than one entry then there will
be 0's and then one total unit amount for the last entry for that client.
I've been toying with if statements. What I meant by tally is a counter I am
thinkning in C an if test would prove true then one would be added to the
counter. But tally doesn't matter I guess I explained it wrong. I hope this
is clearer, thanks for your help and any suggestions.
"Tom Ogilvy" wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks