Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Working with formulas

I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7. I
would want to divide 7 into 1 for a percent. I have been able to accumulate
the columns but not get a percent. This is the formula that I have so far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default Working with formulas

Daniell,

I order to calculate based on the colors of a cell, you have to create your
own UDF (user-defined function).
See here for details: http://www.cpearson.com/excel/colors.htm

--
Brevity is the soul of wit.


"Sandy Mann" wrote:

Daniell,

I may not be understanding you fully but try:

=SUMPRODUCT(($I$1:$I$99="Green")*($A$1:$A$99="Stoc k")*(B1:B99))/SUM(B1:B99)

and format the cell as percentage.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Daniell" wrote in message
...
I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the
total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7.
I
would want to divide 7 into 1 for a percent. I have been able to
accumulate
the columns but not get a percent. This is the formula that I have so
far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Working with formulas

Sandy, that is what I am attempting to do thank. Coloor coding the ceel then
checking the color might be something to look at.

"Dave F" wrote:

Daniell,

I order to calculate based on the colors of a cell, you have to create your
own UDF (user-defined function).
See here for details: http://www.cpearson.com/excel/colors.htm

--
Brevity is the soul of wit.


"Sandy Mann" wrote:

Daniell,

I may not be understanding you fully but try:

=SUMPRODUCT(($I$1:$I$99="Green")*($A$1:$A$99="Stoc k")*(B1:B99))/SUM(B1:B99)

and format the cell as percentage.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Daniell" wrote in message
...
I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the
total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7.
I
would want to divide 7 into 1 for a percent. I have been able to
accumulate
the columns but not get a percent. This is the formula that I have so
far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
All of a sudden none of my Excel formulas are working... Trina Excel Worksheet Functions 3 July 1st 08 10:58 PM
Sumproduct formulas not working after editing dave roth Excel Worksheet Functions 1 February 13th 06 09:37 PM
Countif formulas change after doing a sort Bob Smith Excel Worksheet Functions 3 January 3rd 06 11:17 PM
why formulas have stopped working in Excel? queenjude Excel Worksheet Functions 3 August 16th 05 08:37 PM
Formulas not working after transfering to new workbook Mark Jackson Excel Worksheet Functions 5 December 15th 04 06:17 PM


All times are GMT +1. The time now is 03:47 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"