Formulas

amartin-kelly · External Since: Sep 30, 2005 Posts: 1

How would I set up a formula that consists of a sum on page 1 + a sum on
page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?

Don Guillett · External Since: Sep 01, 2003 Posts: 1150

if all in the same cell
=sum(page1:pag5!a1)

--
Don Guillett
SalesAid Software
donaldb RemoveThis @281.com
"amartin-kelly" <amartin-kelly RemoveThis @discussions.microsoft.com> wrote in message
news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> How would I set up a formula that consists of a sum on page 1 + a sum on
> page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?

amartin-kelly · External Since: Sep 30, 2005 Posts: 3

hi
thank you for your information but when i do that (which is makes total
sense now that i think about it) ..i write the formula as
=sum(week1:week5!a Cool

because we named the pages "week"
after i finish the formula it says something like #NAME!*? and tells me that
the name for "week1" is unrecongnizable.
any thoughts
thanks

"Don Guillett" wrote:

> if all in the same cell
> =sum(page1:pag5!a1)
>
> --
> Don Guillett
> SalesAid Software
> donaldb.TakeThisOut@281.com
> "amartin-kelly" <amartin-kelly.TakeThisOut@discussions.microsoft.com> wrote in message
> news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> > How would I set up a formula that consists of a sum on page 1 + a sum on
> > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
>
>
>

Don Guillett · External Since: Sep 01, 2003 Posts: 1150

Funny, I just tested with a sheet named week1 and a sheet named week5.
Perhaps you have spaces or a leading or trailing space?

--
Don Guillett
SalesAid Software
donaldb RemoveThis @281.com
"amartin-kelly" <amartinkelly RemoveThis @discussions.microsoft.com> wrote in message
news:6D115649-0E26-40AD-BA52-8D303F906E67@microsoft.com...
> hi
> thank you for your information but when i do that (which is makes total
> sense now that i think about it) ..i write the formula as
> =sum(week1:week5!a Cool

> because we named the pages "week"
> after i finish the formula it says something like #NAME!*? and tells me
that
> the name for "week1" is unrecongnizable.
> any thoughts
> thanks
>
> "Don Guillett" wrote:
>
> > if all in the same cell
> > =sum(page1:pag5!a1)
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb RemoveThis @281.com
> > "amartin-kelly" <amartin-kelly RemoveThis @discussions.microsoft.com> wrote in
message
> > news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> > > How would I set up a formula that consists of a sum on page 1 + a sum
on
> > > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
> >
> >
> >

Roger Govier · External Since: Sep 09, 2005 Posts: 1005

Hi

Do you mean =SUM(Sheet1:Sheet5!A1:A20)

Regards

Roger Govier

amartin-kelly wrote:
> How would I set up a formula that consists of a sum on page 1 + a sum on
> page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?

amartin-kelly · External Since: Sep 30, 2005 Posts: 3

Thank You I figured it out and changed the names to the sheets with no spaces!
Thank you so much!! This post board has helped me out so much!

"Don Guillett" wrote:

> Funny, I just tested with a sheet named week1 and a sheet named week5.
> Perhaps you have spaces or a leading or trailing space?
>
> --
> Don Guillett
> SalesAid Software
> donaldb.RemoveThis@281.com
> "amartin-kelly" <amartinkelly.RemoveThis@discussions.microsoft.com> wrote in message
> news:6D115649-0E26-40AD-BA52-8D303F906E67@microsoft.com...
> > hi
> > thank you for your information but when i do that (which is makes total
> > sense now that i think about it) ..i write the formula as
> > =sum(week1:week5!a Cool

> > because we named the pages "week"
> > after i finish the formula it says something like #NAME!*? and tells me
> that
> > the name for "week1" is unrecongnizable.
> > any thoughts
> > thanks
> >
> > "Don Guillett" wrote:
> >
> > > if all in the same cell
> > > =sum(page1:pag5!a1)
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > donaldb.RemoveThis@281.com
> > > "amartin-kelly" <amartin-kelly.RemoveThis@discussions.microsoft.com> wrote in
> message
> > > news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> > > > How would I set up a formula that consists of a sum on page 1 + a sum
> on
> > > > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
> > >
> > >
> > >
>
>
>

Don Guillett · External Since: Sep 01, 2003 Posts: 1150

glad to help

--
Don Guillett
SalesAid Software
donaldb DeleteThis @281.com
"amartin-kelly" <amartinkelly DeleteThis @discussions.microsoft.com> wrote in message
news:45B7403B-C914-4F78-B3FF-534C359A0B6E@microsoft.com...
> Thank You I figured it out and changed the names to the sheets with no
spaces!
> Thank you so much!! This post board has helped me out so much!
>
> "Don Guillett" wrote:
>
> > Funny, I just tested with a sheet named week1 and a sheet named week5.
> > Perhaps you have spaces or a leading or trailing space?
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb DeleteThis @281.com
> > "amartin-kelly" <amartinkelly DeleteThis @discussions.microsoft.com> wrote in
message
> > news:6D115649-0E26-40AD-BA52-8D303F906E67@microsoft.com...
> > > hi
> > > thank you for your information but when i do that (which is makes
total
> > > sense now that i think about it) ..i write the formula as
> > > =sum(week1:week5!a Cool

> > > because we named the pages "week"
> > > after i finish the formula it says something like #NAME!*? and tells
me
> > that
> > > the name for "week1" is unrecongnizable.
> > > any thoughts
> > > thanks
> > >
> > > "Don Guillett" wrote:
> > >
> > > > if all in the same cell
> > > > =sum(page1:pag5!a1)
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > donaldb DeleteThis @281.com
> > > > "amartin-kelly" <amartin-kelly DeleteThis @discussions.microsoft.com> wrote in
> > message
> > > > news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> > > > > How would I set up a formula that consists of a sum on page 1 + a
sum
> > on
> > > > > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
> > > >
> > > >
> > > >
> >
> >
> >

davidgrandma · External Since: Jun 01, 2009 Posts: 1

I just read your response to a formula question and you seem pretty
knowledgeable. I have 3 columns: Goal, Actual and Base. I am trying to find
a formula that will compare the amounts in both columns and post the correct
amount in Base.
If the amount in actual is more than the amount in goal then base = goal,
but if actual is less than goal, then the base is actual.

I can't quite get the formula for this. Do you have any clues for me?

Thanks

Donna

"Don Guillett" wrote:

> Funny, I just tested with a sheet named week1 and a sheet named week5.
> Perhaps you have spaces or a leading or trailing space?
>
> --
> Don Guillett
> SalesAid Software
> donaldb DeleteThis @281.com
> "amartin-kelly" <amartinkelly DeleteThis @discussions.microsoft.com> wrote in message
> news:6D115649-0E26-40AD-BA52-8D303F906E67@microsoft.com...
> > hi
> > thank you for your information but when i do that (which is makes total
> > sense now that i think about it) ..i write the formula as
> > =sum(week1:week5!a Cool

> > because we named the pages "week"
> > after i finish the formula it says something like #NAME!*? and tells me
> that
> > the name for "week1" is unrecongnizable.
> > any thoughts
> > thanks
> >
> > "Don Guillett" wrote:
> >
> > > if all in the same cell
> > > =sum(page1:pag5!a1)
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > donaldb DeleteThis @281.com
> > > "amartin-kelly" <amartin-kelly DeleteThis @discussions.microsoft.com> wrote in
> message
> > > news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> > > > How would I set up a formula that consists of a sum on page 1 + a sum
> on
> > > > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
> > >
> > >
> > >
>
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3429

You're missing a condition...

What if Goal is equal to Actual?

Goal = 100
Actual = 100

What happens in that case?

--
Biff
Microsoft Excel MVP

"davidgrandma" <davidgrandma RemoveThis @discussions.microsoft.com> wrote in message
news:1D54E09B-E7AE-490C-A1B7-0D156FFCD5E0@microsoft.com...
>I just read your response to a formula question and you seem pretty
> knowledgeable. I have 3 columns: Goal, Actual and Base. I am trying to
> find
> a formula that will compare the amounts in both columns and post the
> correct
> amount in Base.
> If the amount in actual is more than the amount in goal then base = goal,
> but if actual is less than goal, then the base is actual.
>
> I can't quite get the formula for this. Do you have any clues for me?
>
> Thanks
>
> Donna
>
>
> "Don Guillett" wrote:
>
>> Funny, I just tested with a sheet named week1 and a sheet named week5.
>> Perhaps you have spaces or a leading or trailing space?
>>
>> --
>> Don Guillett
>> SalesAid Software
>> donaldb RemoveThis @281.com
>> "amartin-kelly" <amartinkelly RemoveThis @discussions.microsoft.com> wrote in message
>> news:6D115649-0E26-40AD-BA52-8D303F906E67@microsoft.com...
>> > hi
>> > thank you for your information but when i do that (which is makes total
>> > sense now that i think about it) ..i write the formula as
>> > =sum(week1:week5!a Cool

>> > because we named the pages "week"
>> > after i finish the formula it says something like #NAME!*? and tells me
>> that
>> > the name for "week1" is unrecongnizable.
>> > any thoughts
>> > thanks
>> >
>> > "Don Guillett" wrote:
>> >
>> > > if all in the same cell
>> > > =sum(page1:pag5!a1)
>> > >
>> > > --
>> > > Don Guillett
>> > > SalesAid Software
>> > > donaldb RemoveThis @281.com
>> > > "amartin-kelly" <amartin-kelly RemoveThis @discussions.microsoft.com> wrote in
>> message
>> > > news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
>> > > > How would I set up a formula that consists of a sum on page 1 + a
>> > > > sum
>> on
>> > > > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
>> > >
>> > >
>> > >
>>
>>
>>

Bernard Liengme · External Since: Jan 27, 2004 Posts: 2596

Let Goal in in A1, Actual in B1
In the corresponding Base cell:
=IF(B1>A1, A1, B1)
read as If (actual greater than base) then give me Goal, else give be Actual

Of course, this =MIN(A1,B1) does the same thing

But as Biff asks what if they are the same? If they are both 4 then either
formula will give 4
If you want something more complex please let us know
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"davidgrandma" <davidgrandma DeleteThis @discussions.microsoft.com> wrote in message
news:1D54E09B-E7AE-490C-A1B7-0D156FFCD5E0@microsoft.com...
>I just read your response to a formula question and you seem pretty
> knowledgeable. I have 3 columns: Goal, Actual and Base. I am trying to
> find
> a formula that will compare the amounts in both columns and post the
> correct
> amount in Base.
> If the amount in actual is more than the amount in goal then base = goal,
> but if actual is less than goal, then the base is actual.
>
> I can't quite get the formula for this. Do you have any clues for me?
>
> Thanks
>
> Donna
>
>
> "Don Guillett" wrote:
>
>> Funny, I just tested with a sheet named week1 and a sheet named week5.
>> Perhaps you have spaces or a leading or trailing space?
>>
>> --
>> Don Guillett
>> SalesAid Software
>> donaldb DeleteThis @281.com
>> "amartin-kelly" <amartinkelly DeleteThis @discussions.microsoft.com> wrote in message
>> news:6D115649-0E26-40AD-BA52-8D303F906E67@microsoft.com...
>> > hi
>> > thank you for your information but when i do that (which is makes total
>> > sense now that i think about it) ..i write the formula as
>> > =sum(week1:week5!a Cool

>> > because we named the pages "week"
>> > after i finish the formula it says something like #NAME!*? and tells me
>> that
>> > the name for "week1" is unrecongnizable.
>> > any thoughts
>> > thanks
>> >
>> > "Don Guillett" wrote:
>> >
>> > > if all in the same cell
>> > > =sum(page1:pag5!a1)
>> > >
>> > > --
>> > > Don Guillett
>> > > SalesAid Software
>> > > donaldb DeleteThis @281.com
>> > > "amartin-kelly" <amartin-kelly DeleteThis @discussions.microsoft.com> wrote in
>> message
>> > > news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
>> > > > How would I set up a formula that consists of a sum on page 1 + a
>> > > > sum
>> on
>> > > > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
>> > >
>> > >
>> > >
>>
>>
>>

Pat · External Since: Nov 19, 2004 Posts: 243

I have the same issue, How would I set up a formula that consists of a sum
on page 1 + a sum on page 2 + a sum on page 3 + a sum on page 4 to = out on
page 5,
but the numbers are not all in the same cell. Please help

"Don Guillett" wrote:

> if all in the same cell
> =sum(page1:pag5!a1)
>
> --
> Don Guillett
> SalesAid Software
> donaldb DeleteThis @281.com
> "amartin-kelly" <amartin-kelly DeleteThis @discussions.microsoft.com> wrote in message
> news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> > How would I set up a formula that consists of a sum on page 1 + a sum on
> > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
>
>
>

Don Guillett · External Since: Jan 04, 2006 Posts: 2988

=SUM(Sheet1:Sheet4!A1:A21)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1 DeleteThis @austin.rr.com
"Pat" <Pat DeleteThis @discussions.microsoft.com> wrote in message
news:1AB91D36-FF21-48F5-A991-6CDA9CE0DAED@microsoft.com...
>I have the same issue, How would I set up a formula that consists of a sum
> on page 1 + a sum on page 2 + a sum on page 3 + a sum on page 4 to = out
> on
> page 5,
> but the numbers are not all in the same cell. Please help
>
> "Don Guillett" wrote:
>
>> if all in the same cell
>> =sum(page1:pag5!a1)
>>
>> --
>> Don Guillett
>> SalesAid Software
>> donaldb DeleteThis @281.com
>> "amartin-kelly" <amartin-kelly DeleteThis @discussions.microsoft.com> wrote in
>> message
>> news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
>> > How would I set up a formula that consists of a sum on page 1 + a sum
>> > on
>> > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
>>
>>
>>

Pat · External Since: Nov 19, 2004 Posts: 243

I have multiple lines that will require the same type of data, how can I
include those calculations in the formula?

"Don Guillett" wrote:

> =SUM(Sheet1:Sheet4!A1:A21)
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1.TakeThisOut@austin.rr.com
> "Pat" <Pat.TakeThisOut@discussions.microsoft.com> wrote in message
> news:1AB91D36-FF21-48F5-A991-6CDA9CE0DAED@microsoft.com...
> >I have the same issue, How would I set up a formula that consists of a sum
> > on page 1 + a sum on page 2 + a sum on page 3 + a sum on page 4 to = out
> > on
> > page 5,
> > but the numbers are not all in the same cell. Please help
> >
> > "Don Guillett" wrote:
> >
> >> if all in the same cell
> >> =sum(page1:pag5!a1)
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> donaldb.TakeThisOut@281.com
> >> "amartin-kelly" <amartin-kelly.TakeThisOut@discussions.microsoft.com> wrote in
> >> message
> >> news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> >> > How would I set up a formula that consists of a sum on page 1 + a sum
> >> > on
> >> > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
> >>
> >>
> >>
>
>

Pat · External Since: Nov 19, 2004 Posts: 243

I just realized I left out that the sums are of different columns on each
worksheet.

"Don Guillett" wrote:

> =SUM(Sheet1:Sheet4!A1:A21)
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1.RemoveThis@austin.rr.com
> "Pat" <Pat.RemoveThis@discussions.microsoft.com> wrote in message
> news:1AB91D36-FF21-48F5-A991-6CDA9CE0DAED@microsoft.com...
> >I have the same issue, How would I set up a formula that consists of a sum
> > on page 1 + a sum on page 2 + a sum on page 3 + a sum on page 4 to = out
> > on
> > page 5,
> > but the numbers are not all in the same cell. Please help
> >
> > "Don Guillett" wrote:
> >
> >> if all in the same cell
> >> =sum(page1:pag5!a1)
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> donaldb.RemoveThis@281.com
> >> "amartin-kelly" <amartin-kelly.RemoveThis@discussions.microsoft.com> wrote in
> >> message
> >> news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
> >> > How would I set up a formula that consists of a sum on page 1 + a sum
> >> > on
> >> > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
> >>
> >>
> >>
>
>

Don Guillett · External Since: Jan 04, 2006 Posts: 2988

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.RemoveThis@austin.rr.com
"Pat" <Pat.RemoveThis@discussions.microsoft.com> wrote in message
news:B2B3E56B-C3AA-43BC-8E54-18027035343F@microsoft.com...
>I just realized I left out that the sums are of different columns on each
> worksheet.
>
> "Don Guillett" wrote:
>
>> =SUM(Sheet1:Sheet4!A1:A21)
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1.RemoveThis@austin.rr.com
>> "Pat" <Pat.RemoveThis@discussions.microsoft.com> wrote in message
>> news:1AB91D36-FF21-48F5-A991-6CDA9CE0DAED@microsoft.com...
>> >I have the same issue, How would I set up a formula that consists of a
>> >sum
>> > on page 1 + a sum on page 2 + a sum on page 3 + a sum on page 4 to =
>> > out
>> > on
>> > page 5,
>> > but the numbers are not all in the same cell. Please help
>> >
>> > "Don Guillett" wrote:
>> >
>> >> if all in the same cell
>> >> =sum(page1:pag5!a1)
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> donaldb.RemoveThis@281.com
>> >> "amartin-kelly" <amartin-kelly.RemoveThis@discussions.microsoft.com> wrote in
>> >> message
>> >> news:36881BBC-B95D-40D0-9709-55E346BB091B@microsoft.com...
>> >> > How would I set up a formula that consists of a sum on page 1 + a
>> >> > sum
>> >> > on
>> >> > page 2 + a sum on page 3 + a sum on page 4 to = out on page 5?
>> >>
>> >>
>> >>
>>
>>