How do I sum and add * on each side based on value in excel?

Earl · 04-05-2005, 05:06 PM

I have four columns of data. The first is the maximum no. allowed, the second
is the last period total, the third is the current period total and the
fourth is the sum of two and three. The typical sum function works fine until
the fourth column equals the first, this is where I want to place an asterisk
before and after the sum. This would indicate that the vlue has reached it's
max.

Is this possible?

Thanks.

Ken Hudson · 04-05-2005, 06:06 PM

Hi Earl,
Try this in column D:

=IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)

"Earl" wrote:

> I have four columns of data. The first is the maximum no. allowed, the second
> is the last period total, the third is the current period total and the
> fourth is the sum of two and three. The typical sum function works fine until
> the fourth column equals the first, this is where I want to place an asterisk
> before and after the sum. This would indicate that the vlue has reached it's
> max.
>
> Is this possible?
>
> Thanks.
>
>

Earl · 04-06-2005, 10:06 AM

Ken,
Thx for the reply.
When I apply the formula it returns *0* whether column D has reached it's
max. or not.

The closest I have gotten is; =IF(B1+C1=A1,"Maximum Reached",SUM(B1+C1)) .

This is not what I want but maybe the only solution.

"Ken Hudson" wrote:

> Hi Earl,
> Try this in column D:
>
> =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
>
> "Earl" wrote:
>
> > I have four columns of data. The first is the maximum no. allowed, the second
> > is the last period total, the third is the current period total and the
> > fourth is the sum of two and three. The typical sum function works fine until
> > the fourth column equals the first, this is where I want to place an asterisk
> > before and after the sum. This would indicate that the vlue has reached it's
> > max.
> >
> > Is this possible?
> >
> > Thanks.
> >
> >

JulieD · 04-06-2005, 12:06 PM

Hi Earl

try
=IF(AND(B1<>"",C1<>"",A1<>"",B1+C1=A1),"*"&B1+C1&"*",B1+C1)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Earl" <[email protected]> wrote in message
news:[email protected]...
> Ken,
> Thx for the reply.
> When I apply the formula it returns *0* whether column D has reached it's
> max. or not.
>
> The closest I have gotten is; =IF(B1+C1=A1,"Maximum Reached",SUM(B1+C1))
> .
>
> This is not what I want but maybe the only solution.
>
>
> "Ken Hudson" wrote:
>
>> Hi Earl,
>> Try this in column D:
>>
>> =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
>>
>> "Earl" wrote:
>>
>> > I have four columns of data. The first is the maximum no. allowed, the
>> > second
>> > is the last period total, the third is the current period total and the
>> > fourth is the sum of two and three. The typical sum function works fine
>> > until
>> > the fourth column equals the first, this is where I want to place an
>> > asterisk
>> > before and after the sum. This would indicate that the vlue has reached
>> > it's
>> > max.
>> >
>> > Is this possible?
>> >
>> > Thanks.
>> >
>> >

Ken Hudson · 04-06-2005, 12:06 PM

Earl,
Sorry, I guess I didn't understand your data/question. Could you please put
up a few sample rows and your expected result in column D to look at?

"Earl" wrote:

> Ken,
> Thx for the reply.
> When I apply the formula it returns *0* whether column D has reached it's
> max. or not.
>
> The closest I have gotten is; =IF(B1+C1=A1,"Maximum Reached",SUM(B1+C1)) .
>
> This is not what I want but maybe the only solution.
>
>
> "Ken Hudson" wrote:
>
> > Hi Earl,
> > Try this in column D:
> >
> > =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
> >
> > "Earl" wrote:
> >
> > > I have four columns of data. The first is the maximum no. allowed, the second
> > > is the last period total, the third is the current period total and the
> > > fourth is the sum of two and three. The typical sum function works fine until
> > > the fourth column equals the first, this is where I want to place an asterisk
> > > before and after the sum. This would indicate that the vlue has reached it's
> > > max.
> > >
> > > Is this possible?
> > >
> > > Thanks.
> > >
> > >

Earl · 04-06-2005, 12:06 PM

JulieD:

The formula is exactly what I was looking for.

One follow up thought: Is there a way to amend the formula to 'flag' if the
values entered exceed the maximum?

Thx.
Earl
"JulieD" wrote:

> Hi Earl
>
> try
> =IF(AND(B1<>"",C1<>"",A1<>"",B1+C1=A1),"*"&B1+C1&"*",B1+C1)
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Earl" <[email protected]> wrote in message
> news:[email protected]...
> > Ken,
> > Thx for the reply.
> > When I apply the formula it returns *0* whether column D has reached it's
> > max. or not.
> >
> > The closest I have gotten is; =IF(B1+C1=A1,"Maximum Reached",SUM(B1+C1))
> > .
> >
> > This is not what I want but maybe the only solution.
> >
> >
> > "Ken Hudson" wrote:
> >
> >> Hi Earl,
> >> Try this in column D:
> >>
> >> =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
> >>
> >> "Earl" wrote:
> >>
> >> > I have four columns of data. The first is the maximum no. allowed, the
> >> > second
> >> > is the last period total, the third is the current period total and the
> >> > fourth is the sum of two and three. The typical sum function works fine
> >> > until
> >> > the fourth column equals the first, this is where I want to place an
> >> > asterisk
> >> > before and after the sum. This would indicate that the vlue has reached
> >> > it's
> >> > max.
> >> >
> >> > Is this possible?
> >> >
> >> > Thanks.
> >> >
> >> >
>
>
>

JulieD · 04-06-2005, 12:06 PM

Hi Earl

glad we're getting there, how about
=IF(COUNT(A1:C1)=3,IF(B1+C1>A1,"Exceeded",IF(B1+C1=A1,"*"&B1+C1&"*",B1+C1)),B1+C1)
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Earl" <[email protected]> wrote in message
news:[email protected]...
> JulieD:
>
> The formula is exactly what I was looking for.
>
> One follow up thought: Is there a way to amend the formula to 'flag' if
> the
> values entered exceed the maximum?
>
> Thx.
> Earl
> "JulieD" wrote:
>
>> Hi Earl
>>
>> try
>> =IF(AND(B1<>"",C1<>"",A1<>"",B1+C1=A1),"*"&B1+C1&"*",B1+C1)
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Earl" <[email protected]> wrote in message
>> news:[email protected]...
>> > Ken,
>> > Thx for the reply.
>> > When I apply the formula it returns *0* whether column D has reached
>> > it's
>> > max. or not.
>> >
>> > The closest I have gotten is; =IF(B1+C1=A1,"Maximum
>> > Reached",SUM(B1+C1))
>> > .
>> >
>> > This is not what I want but maybe the only solution.
>> >
>> >
>> > "Ken Hudson" wrote:
>> >
>> >> Hi Earl,
>> >> Try this in column D:
>> >>
>> >> =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
>> >>
>> >> "Earl" wrote:
>> >>
>> >> > I have four columns of data. The first is the maximum no. allowed,
>> >> > the
>> >> > second
>> >> > is the last period total, the third is the current period total and
>> >> > the
>> >> > fourth is the sum of two and three. The typical sum function works
>> >> > fine
>> >> > until
>> >> > the fourth column equals the first, this is where I want to place an
>> >> > asterisk
>> >> > before and after the sum. This would indicate that the vlue has
>> >> > reached
>> >> > it's
>> >> > max.
>> >> >
>> >> > Is this possible?
>> >> >
>> >> > Thanks.
>> >> >
>> >> >
>>
>>
>>

Earl · 04-06-2005, 01:06 PM

Thx. This is exactly what I wanted.

"JulieD" wrote:

> Hi Earl
>
> glad we're getting there, how about
> =IF(COUNT(A1:C1)=3,IF(B1+C1>A1,"Exceeded",IF(B1+C1=A1,"*"&B1+C1&"*",B1+C1)),B1+C1)
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Earl" <[email protected]> wrote in message
> news:[email protected]...
> > JulieD:
> >
> > The formula is exactly what I was looking for.
> >
> > One follow up thought: Is there a way to amend the formula to 'flag' if
> > the
> > values entered exceed the maximum?
> >
> > Thx.
> > Earl
> > "JulieD" wrote:
> >
> >> Hi Earl
> >>
> >> try
> >> =IF(AND(B1<>"",C1<>"",A1<>"",B1+C1=A1),"*"&B1+C1&"*",B1+C1)
> >>
> >> --
> >> Cheers
> >> JulieD
> >> check out www.hcts.net.au/tipsandtricks.htm
> >> ....well i'm working on it anyway
> >> "Earl" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Ken,
> >> > Thx for the reply.
> >> > When I apply the formula it returns *0* whether column D has reached
> >> > it's
> >> > max. or not.
> >> >
> >> > The closest I have gotten is; =IF(B1+C1=A1,"Maximum
> >> > Reached",SUM(B1+C1))
> >> > .
> >> >
> >> > This is not what I want but maybe the only solution.
> >> >
> >> >
> >> > "Ken Hudson" wrote:
> >> >
> >> >> Hi Earl,
> >> >> Try this in column D:
> >> >>
> >> >> =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
> >> >>
> >> >> "Earl" wrote:
> >> >>
> >> >> > I have four columns of data. The first is the maximum no. allowed,
> >> >> > the
> >> >> > second
> >> >> > is the last period total, the third is the current period total and
> >> >> > the
> >> >> > fourth is the sum of two and three. The typical sum function works
> >> >> > fine
> >> >> > until
> >> >> > the fourth column equals the first, this is where I want to place an
> >> >> > asterisk
> >> >> > before and after the sum. This would indicate that the vlue has
> >> >> > reached
> >> >> > it's
> >> >> > max.
> >> >> >
> >> >> > Is this possible?
> >> >> >
> >> >> > Thanks.
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>

JulieD · 04-06-2005, 01:06 PM

you're welcome

"Earl" <[email protected]> wrote in message
news:[email protected]...
> Thx. This is exactly what I wanted.
>
> "JulieD" wrote:
>
>> Hi Earl
>>
>> glad we're getting there, how about
>> =IF(COUNT(A1:C1)=3,IF(B1+C1>A1,"Exceeded",IF(B1+C1=A1,"*"&B1+C1&"*",B1+C1)),B1+C1)
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Earl" <[email protected]> wrote in message
>> news:[email protected]...
>> > JulieD:
>> >
>> > The formula is exactly what I was looking for.
>> >
>> > One follow up thought: Is there a way to amend the formula to 'flag'
>> > if
>> > the
>> > values entered exceed the maximum?
>> >
>> > Thx.
>> > Earl
>> > "JulieD" wrote:
>> >
>> >> Hi Earl
>> >>
>> >> try
>> >> =IF(AND(B1<>"",C1<>"",A1<>"",B1+C1=A1),"*"&B1+C1&"*",B1+C1)
>> >>
>> >> --
>> >> Cheers
>> >> JulieD
>> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> ....well i'm working on it anyway
>> >> "Earl" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Ken,
>> >> > Thx for the reply.
>> >> > When I apply the formula it returns *0* whether column D has reached
>> >> > it's
>> >> > max. or not.
>> >> >
>> >> > The closest I have gotten is; =IF(B1+C1=A1,"Maximum
>> >> > Reached",SUM(B1+C1))
>> >> > .
>> >> >
>> >> > This is not what I want but maybe the only solution.
>> >> >
>> >> >
>> >> > "Ken Hudson" wrote:
>> >> >
>> >> >> Hi Earl,
>> >> >> Try this in column D:
>> >> >>
>> >> >> =IF(B1+C1=A1,"*" & B1+C1 & "*",B1+C1)
>> >> >>
>> >> >> "Earl" wrote:
>> >> >>
>> >> >> > I have four columns of data. The first is the maximum no.
>> >> >> > allowed,
>> >> >> > the
>> >> >> > second
>> >> >> > is the last period total, the third is the current period total
>> >> >> > and
>> >> >> > the
>> >> >> > fourth is the sum of two and three. The typical sum function
>> >> >> > works
>> >> >> > fine
>> >> >> > until
>> >> >> > the fourth column equals the first, this is where I want to place
>> >> >> > an
>> >> >> > asterisk
>> >> >> > before and after the sum. This would indicate that the vlue has
>> >> >> > reached
>> >> >> > it's
>> >> >> > max.
>> >> >> >
>> >> >> > Is this possible?
>> >> >> >
>> >> >> > Thanks.
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>
>>
>>