If formula

[Peter]

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.

 

[Pete_UK]

Try this:

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

Adjust the ranges to suit your real data.

Hope this helps.

Pete

 

[Sandy Mann]

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peter]

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

 

[Peter]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peter]

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)

 

[Peter]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

2488.02/2 = 3970.00 ?????

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peter]

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

 

[Pete_UK]

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

Pete

 

[Toby]

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

 

[Gord Dibben]

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