Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

Try this:

=SUMIF(B1:B4,"2C",A1:A4)/2

Adjust the ranges to suit your real data.

Hope this helps.

Pete

"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

Hi Pete, thanks for looking into this, however this formula does not give the
correct total. 8390.60+5127.10+7940.44 / 2 = 7962.36 (my mistake on original
post was put total of 2cs without dividing (15924.72)

Formula is total all 2cs then divding total by 2, I need it to divide
individually

i.e. 8390.6/2 = 4154.8
5127.10/2 = 2563.55
7940.44/2 = 3970.22

Total = 10688.57

"Pete_UK" wrote:

Try this:

=SUMIF(B1:B4,"2C",A1:A4)/2

Adjust the ranges to suit your real data.

Hope this helps.

Pete

"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 7962.36)

Hope this makes sense.

Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have been
7962.36

So the formula provided does not work - this is because it adds all 2Cs
together and then divides - I need formula to divide each cell individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57? If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have been
7962.36

So the formula provided does not work - this is because it adds all 2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

You may need to do some revision on your knowledge of algebra. =A/2+B/2+C/2
is the same as =(A+B+C)/2.

The reason you are getting a different answer from what you expected is that
you have asked a different question.
The answer 7962.36 comes not from (8390.60+5127.10+7940.44) / 2 but from
(8309.60 + 2488.02 + 5127.10) / 2, which were the numbers you gave in your
question. Sort out what question you are trying to ask, and then you might
get the right answer.
--
David Biddulph

"Peter" wrote in message
...
Hi Pete, thanks for looking into this, however this formula does not give
the
correct total. 8390.60+5127.10+7940.44 / 2 = 7962.36 (my mistake on
original
post was put total of 2cs without dividing (15924.72)

Formula is total all 2cs then divding total by 2, I need it to divide
individually

i.e. 8390.6/2 = 4154.8
5127.10/2 = 2563.55
7940.44/2 = 3970.22

Total = 10688.57

"Pete_UK" wrote:

Try this:

=SUMIF(B1:B4,"2C",A1:A4)/2

Adjust the ranges to suit your real data.

Hope this helps.

Pete

"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this
case
would be 10688.57 NOT 7962.36)

Hope this makes sense.

Just seen your reply to Pete, where did the 7940.44 come from? you had
2488.02 in you OP

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57? If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have
been
7962.36

So the formula provided does not work - this is because it adds all 2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

Hi Sandy

I am after 10688.57 - this is done by

i.e. 8390.6/2 = 4154.8
5127.10/2 = 2563.55
7940.44/2 = 3970.22

Total = 10688.57

I am not looking to divide the sum by 2 - only the individual elements
(sorry if it looked that way)





"Sandy Mann" wrote:

Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57? If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have been
7962.36

So the formula provided does not work - this is because it adds all 2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

mmm, me thinks me is losing the plot.

Lets try again.

8309.60/2 = 4154.80
5127.10/2 = 2563.55
2488.02/2 = 3970.00

sum = 10688.57

Need to get this when criteria is met



"Sandy Mann" wrote:

Just seen your reply to Pete, where did the 7940.44 come from? you had
2488.02 in you OP

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57? If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have
been
7962.36

So the formula provided does not work - this is because it adds all 2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

That is exactly the total that my, (and Pete_UK's) formula give if you use
those numbers - try it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

I am after 10688.57 - this is done by

i.e. 8390.6/2 = 4154.8
5127.10/2 = 2563.55
7940.44/2 = 3970.22

Total = 10688.57

I am not looking to divide the sum by 2 - only the individual elements
(sorry if it looked that way)





"Sandy Mann" wrote:

Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57? If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have
been
7962.36

So the formula provided does not work - this is because it adds all 2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives
sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

2488.02/2 = 3970.00 ?????

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
mmm, me thinks me is losing the plot.

Lets try again.

8309.60/2 = 4154.80
5127.10/2 = 2563.55
2488.02/2 = 3970.00

sum = 10688.57

Need to get this when criteria is met



"Sandy Mann" wrote:

Just seen your reply to Pete, where did the 7940.44 come from? you had
2488.02 in you OP

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57?
If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have
been
7962.36

So the formula provided does not work - this is because it adds all
2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives
sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in
this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

DOH, Doh Doh Doh Doh..

No wonder I am getting confused - it does help if I have the correct figures
etc.

Sandy, Pete(uk) and David Biddulph - thank you for trying to help someone
who is obviously short sighted and needs to go home for a beer!

Of course - formula that I originally tried and the same formula that you
provided gives me the correct answer

"Sandy Mann" wrote:

2488.02/2 = 3970.00 ?????

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
mmm, me thinks me is losing the plot.

Lets try again.

8309.60/2 = 4154.80
5127.10/2 = 2563.55
2488.02/2 = 3970.00

sum = 10688.57

Need to get this when criteria is met



"Sandy Mann" wrote:

Just seen your reply to Pete, where did the 7940.44 come from? you had
2488.02 in you OP

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. What number are you looking for? 7962.36 or 10688.57?
If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. £15294.72 should have
been
7962.36

So the formula provided does not work - this is because it adds all
2Cs
together and then divides - I need formula to divide each cell
individually
then total


"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives
sum
of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in
this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

Well I'm glad you got it straightened out in the end.

Pete

On Mar 11, 5:06*pm, Peter wrote:
DOH, Doh Doh Doh Doh..

No wonder I am getting confused - it does help if I have the correct figures
etc.

Sandy, Pete(uk) and David Biddulph - thank you for trying to help someone
who is obviously short sighted and needs to go home for a beer!

Of course - formula that I originally tried and the same formula that you
provided gives me the correct answer



"Sandy Mann" wrote:
2488.02/2 = 3970.00 ?????

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Peter" wrote in message
...
mmm, me thinks me is losing the plot.

Lets try again.

8309.60/2 = 4154.80
5127.10/2 = 2563.55
2488.02/2 = 3970.00

sum * * * * = 10688.57

Need to get this when criteria is met

"Sandy Mann" wrote:

Just seen your reply to Pete, where did the 7940.44 come from? you had
2488.02 in you OP

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message
...
Dividing the sum by 2 is the same as individual elements by 2 and then
summing them. *What number are you looking for? 7962.36 or 10688.57?
If
10688.57 how do you arrive at this number?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Peter" wrote in message
...
Hi Sandy

thanks for looking into - sorry for confusion. *£15294.72 should have
been
7962.36

So the formula provided does not work - this is because it adds all
2Cs
together and then divides - I need formula to divide each cell
individually
then total

"Sandy Mann" wrote:

=SUMIF(B1:B4,"2C",A1:A4)/2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets
criteria,
adds a selected range but actully divides each cell by 2 and gives
sum
of
these totals

Example
* * * * * * * * * * * Col A * * * *Col B

Row 1 * * * * * * 8309.60 * * * *2C
Row 2 * * * * * * 5000 * * * * * *2
Row 3 * * * * * * 2488.02 * * * 2C
Row 4 * * * * * * 5127.10 * * * *2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in
this
case
would be 10688.57 NOT 15294.72)

Hope this makes sense.- Hide quoted text -

- Show quoted text -

Hi

can you help I need to highlight dates that are expiring

Row ColumA ColumB ColumC
1 Name DGR MHT
2 Jo 11/02/07 12/04/08

I have a large spread sheet for training puroses and I highlight dates
manally but
I am trying to make it highlight automatically, some training needs doing
every 2 years and some needs doing every 12 months. Hope you can help thanks
anyway if you cant

"Pete_UK" wrote:

Try this:

=SUMIF(B1:B4,"2C",A1:A4)/2

Adjust the ranges to suit your real data.

Hope this helps.

Pete

"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 15294.72)

Hope this makes sense.

Check out help on Condtional Fomatting or go to either or both of these
sites.

http://www.contextures.on.ca/xlCondFormat01.html

http://www.cpearson.com/excel/cformatting.htm

Which dates do you want highlighted and when?

60 days before expiry or similar?


Gord Dibben MS Excel MVP

On Tue, 26 Jan 2010 13:08:01 -0800, Toby
wrote:

Hi

can you help I need to highlight dates that are expiring

Row ColumA ColumB ColumC
1 Name DGR MHT
2 Jo 11/02/07 12/04/08

I have a large spread sheet for training puroses and I highlight dates
manally but
I am trying to make it highlight automatically, some training needs doing
every 2 years and some needs doing every 12 months. Hope you can help thanks
anyway if you cant

"Pete_UK" wrote:

Try this:

=SUMIF(B1:B4,"2C",A1:A4)/2

Adjust the ranges to suit your real data.

Hope this helps.

Pete

"Peter" wrote in message
...
Hi All,

I am looking for a formula that looks at a range, and if meets criteria,
adds a selected range but actully divides each cell by 2 and gives sum of
these totals

Example
Col A Col B

Row 1 8309.60 2C
Row 2 5000 2
Row 3 2488.02 2C
Row 4 5127.10 2C

If B1:b4="2c" add cells in Col A and divide by 2 then total (in this case
would be 10688.57 NOT 15294.72)

Hope this makes sense.