If formula

  • Thread starter Thread starter Peter
  • Start date Start date
P

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.
 
Try this:

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

Adjust the ranges to suit your real data.

Hope this helps.

Pete
 
=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
 
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
 
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
 
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
 
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.
 
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
 
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)
 
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
 
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
 
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
 
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
 
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
 
Back
Top