|
If formula
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. |
If formula
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. |
If formula
=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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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. |
If formula
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 - |
If formula
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. |
If formula
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. |
| All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com