Formula depending on the color of a cell to calculate values

[keranali]

Good Day All can I calculate values based on the colors of a cell. Calculation will be.... For Cell BJ3....if BF3 is red then take BI3 and make it a negative value and For Cell BJ3... If BF3 is Green then take (BM2*BL2)-BI4

Cell Colours

the project 1.3.xlsm
	BF
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Overview

Sample output

the project 1.3.xlsm
	BI	BJ
2	Investment	Profit/Loss
3	$ 110.00	$ (110.00)
4	$ 110.00	$ 60.00
5	$ 110.00
6	$ 110.00
7	$ 110.00
8	$ 110.00
9	$ 110.00
10	$ 110.00
11	$ 110.00
12	$ 110.00
13	$ 110.00
14	$ 110.00
15	$ 110.00
16	$ 110.00
17	$ 110.00
18	$ 110.00
19	$ 110.00
20	$ 110.00
21	$ 110.00
22	$ 110.00
23	$ 110.00
24	$ 110.00
25	$ 110.00
26	$ 110.00
27	$ 110.00
Overview

Cell Formulas
Range	Formula
BJ3	BJ3	=-BI3
BJ4	BJ4	=(BM2*BL2)-BI4
BI3:BI27	BI3	=$BM$2*$BN$2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BH3:BI28	Expression	=AND(BG3<>"",ISEVEN(BG3))	text	NO
BH3:BI28	Expression	=ISODD(BG3)	text	NO

Adjustable values

the project 1.3.xlsm
	BL	BM	BN
2	34	5	22
Overview

 

[etaf]

you are using conditional formatting for RED
using ISODD(BG3)

you could use that in a calculation in a different cell to do that
I'm assuming if not red , then green - or is there a green rule

otherwise , would need VBA to check cell colour and also update the value , i do not provide vba solutions here

 

[keranali]

Thanks for your reply Both colours will have a rule

 

[etaf]

if this is based on ODD or EVEN numbers

then you can
SUM ODD numbers using
=SUM(B3:B10*(MOD(B3:B10,2)=1))

SUM EVEN numbers using
=SUM(B3:B10*(MOD(B3:B10,2)=0))

as you have 365 version it will apply the array
on older versions needs Control+Shift+Enter

OR Even rows and ODD rows
ODD ROWS
=SUMPRODUCT(B3:B17,MOD(ROW(B3:B17)+0,2))
EVEN ROWS
=SUMPRODUCT(B3:B17,MOD(ROW(B3:B17)+1,2))

Sum odd or Even numbers.xlsx
	A	B	C	D	E	F	G
1			Sum Odd	Sum Even		Sum ODD Rows	Sum EVEN roews
2			7	16		18	5
3		12
4		1
5		5
6		4
7		1
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUM(B3:B10*(MOD(B3:B10,2)=1))
D2	D2	=SUM(B3:B10*(MOD(B3:B10,2)=0))
F2	F2	=SUMPRODUCT(B3:B17,MOD(ROW(B3:B17)+0,2))
G2	G2	=SUMPRODUCT(B3:B17,MOD(ROW(B3:B17)+1,2))

 

[keranali]

Its based on number selections maybe instead of colors I this number set can be used e.g

the project 1.3.xlsm
	BJ
2	Actual
3	14
4	33
5	5
6	32
7	35
8	16
9	24
10	16
11	1
12	35
13	16
14	26
15	10
16	36
17	14
18	17
19	18
20	25
21	27
22	15
23	8
24	1
25	7
26	23
27	12
28	11
29	30
30	35
31	27
32	36
33	26
34	14
35	10
Overview

the project 1.3.xlsm
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
3	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	10	19	21	30	25
4	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
5	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
6	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
7	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
8	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
9	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
10	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
11	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
12	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
13	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
14	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
15	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
16	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
17	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
18	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
19	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
20	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
21	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
22	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
23	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
24	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
25	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
26	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
27	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
28	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
29	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
30	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
31	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
32	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
33	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
34	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
35	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
Overview

If a number from the actual table BJ3 is not listed in the larger table then make BM3 a negative value of the figure in BL3 and if the number is listed in larger list then allow this =($BP$2*$BO$2)-$BL$4

 

[keranali]

I am trying to use this However its not working too well =IF(ISNUMBER(MATCH(BJ3,AM3:BH3,0)),-BL3,($AB$4*$AA$4)-$BLA$43+B3)

 

[etaf]

in your example - all the numbers in BJ are listed in the larger table

I dont understand the yellow highlight

I have used a countif() to see if the number in BJ exists in the table and you will see from below it does

Book2
	BJ	BK
2	Actual	Countif
3	14	4
4	33	21
5	5	4
6	32	8
7	35	4
8	16	29
9	24	33
10	16	29
11	1	33
12	35	4
13	16	29
14	26	33
15	10	17
16	36	21
17	14	4
18	17	33
19	18	8
20	25	33
21	27	12
22	15	33
23	8	33
24	1	33
25	7	33
26	23	33
27	12	33
28	11	4
29	30	1
30	35	4
31	27	12
32	36	21
33	26	33
34	14	4
35	10	17
Sheet1

Cell Formulas
Range	Formula
BK3:BK35	BK3	=COUNTIF($AM$3:$BH$35,BJ3)

We could use IF ( COUNTIF($AM$3:$BH$35,BJ3) = 0 , BJ3*-1 , ($BP$2*$BO$2)-$BL$4)
But that ($BP$2*$BO$2)-$BL$4 is fixed and will result in all the same answers , is the formula correct ?

I have also added a conditional formatting rule to show if the BJ number exists in the table

Book2
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
3	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	10	19	21	30	25
4	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
5	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
6	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
7	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20
8	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
9	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
10	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
11	23	26	1	16	25	9	17	31	29	2	7	24	8	12	15	28	36	33	27	4	32	19
12	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
13	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
14	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
15	23	26	1	25	9	17	31	16	29	2	7	8	12	15	24	28	36	33	10	21	13	4
16	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
17	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
18	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
19	23	31	12	26	29	15	1	2	24	25	7	28	9	3	36	17	8	33	13	20	18	27
20	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
21	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
22	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
23	23	26	1	9	31	25	17	19	2	7	16	8	12	15	24	28	36	33	13	21	5	27
24	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
25	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
26	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
27	23	26	1	9	31	2	7	17	25	29	12	16	8	15	24	28	4	22	6	10	34	21
28	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
29	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
30	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
31	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	14	18	35	19
32	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
33	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
34	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
35	23	26	9	31	1	2	17	25	29	7	16	8	12	15	24	28	4	22	34	10	19	32
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AM3:BH35	Expression	=COUNTIF($BJ$3:$BJ$35,AM3)>0	text	NO

I'm afraid i'm really not following what you are after ......

perhaps you can answer the questions above and also give some expected results and why - perhaps on a much smaller set of data

 

[keranali]

Ok sorry for the unclear request I tried redo the workbook and lay it out better.

This is the table of data where the sort will take place in a line row from AM3:BH3. when a number is manually inputted into BJ3 and that number does not appear in the row AM3:BH3, BM3 will multiply BL3* -1 however if the number appears in the row then it should do this =($AB$4*$AA$4)-BL4. see example below:

the project 1.3.xlsm
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
2	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd		Actual	Colour	Investment	Profit/Loss
3	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	10	19	21	30	25		14		220	-220
4	23	26	1	16	9	17	31	29	2	7	24	8	12	15	28	33	36	25	4	11	10	20		33		220	120
Overview

Cell Formulas
Range	Formula
BL3:BL4	BL3	=$AB$4*$AC$4
BM3	BM3	=-BL3
BM4	BM4	=($AB$4*$AA$4)-BL4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BK3:BL39	Expression	=AND(BJ3<>"",ISEVEN(BJ3))	text	NO
BK3:BL39	Expression	=ISODD(BJ3)	text	NO

And the line sort range AM3:BH3 should be dependent on a line count, if the line count should change to e.g 5 then the range will adjust to the 1st to the 5th number

the project 1.3.xlsm
	AA	AB	AC
3	Mutiplier	Nominal	Line Count
4	34	10	22
Overview

 

[etaf]

i'm just a little confused by some of the formulas you are using in the rows ....

BUT taking you at your word

you can use a countif() to see if the number is in the row

=IF(COUNTIF(AM3:BH3,BJ3)=0,BL3*-1,($AB3*$AA3)-BL3)

COUNTIF(AM3:BH3,BJ3)=0 , then the number is not in the row - and so BL3*-1
otherwise
($AB3*$AA3)-BL3

Now i have NOT fixed the ROW - so was not sure if
($AB3*$AA3)-BL3
was correct

but you can see the IF and change

I have also added conditional formatting
simple
AM3=$BJ3

BUT now with the line count, that gets a bit more complicated as that is changing a range based on a number in a different sheet
So the countif() will have to change

at the moment because in Linecount you have 22 we can use
COUNTIF(AM3:BH3,BJ3)=0

BUT if line count was 5 , then that would be
COUNTIF(AM3:AQ3,BJ3)=0

so i think we will need some sort of indirect() ... and columns / offset type of thing
will need to have a think about

if you could just check the $ in the formulas below

Book2
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
2	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd		Actual	Colour	Investment	Profit/Loss
3	23.00	26.00	1.00	16.00	9.00	17.00	31.00	29.00	2.00	7.00	24.00	8.00	12.00	15.00	28.00	33.00	36.00	10.00	19.00	21.00	30.00	25.00		14.00		0	0
4	23.00	26.00	1.00	16.00	9.00	17.00	31.00	29.00	2.00	7.00	24.00	8.00	12.00	15.00	28.00	33.00	36.00	25.00	4.00	11.00	10.00	20.00		33.00		0	0
Sheet1

Cell Formulas
Range	Formula
BL3:BL4	BL3	=$AB$4*$AC$4
BM3:BM4	BM3	=IF(COUNTIF(AM3:BH3,BJ3)=0,BL3*-1,($AB3*$AA3)-BL3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AM3:BH4	Expression	=AM3=$BJ3	text	NO

 

[etaf]

see previous post as well as this one

I have added the range based off the other sheet number

Many ways I guess to do this - I have chosen the below
But maybe OFFSET would be another choice

INDIRECT() is volatile - and so will update every time the sheet updates - which can slow things down
should be able to do with an index, but i could not make it work - so watch out , as i may change that , if i can to a Non-Volatile function if possible

i think this will work
=IF(COUNTIF( AM3:INDIRECT(ADDRESS(ROW(),38+overview!$AC$4)),BJ3)=0,BL3*-1,($AB3*$AA3)-BL3)

Using a sheet named overview and a cell AC4 to find the number of columns to count as a range

the 38 is the starting column 0 so its based on AL
and start at AM3
if a 5 was in the cell
then the range would be
AM3 to AQ3

row() gives the row() the formula is in

Book2
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
2	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd		Actual	Colour	Investment	Profit/Loss
3	23.00	26.00	1.00	16.00	9.00	17.00	31.00	29.00	2.00	7.00	24.00	8.00	12.00	15.00	28.00	33.00	36.00	10.00	19.00	21.00	30.00	25.00		14.00		220	-220
4	23.00	26.00	1.00	16.00	9.00	17.00	31.00	29.00	2.00	7.00	24.00	8.00	12.00	15.00	28.00	33.00	36.00	25.00	4.00	11.00	10.00	20.00		33.00		220	11780
5
6
Sheet1

Cell Formulas
Range	Formula
BM3:BM4	BM3	=IF(COUNTIF( AM3:INDIRECT(ADDRESS(ROW(),38+overview!$AC$4)),BJ3)=0,BL3*-1,($AB3*$AA3)-BL3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AM3:BH4	Expression	=AM3=$BJ3	text	NO

Book2
	AA	AB	AC
3	Mutiplier	Nominal	Line Count
4	34	10	22
overview

I have added to dropbox share the spreadsheet - BUT i only KEEP this on the share for a few days

Formula depending on the color of a cell to calculate values - ETAF.xlsx

Shared with Dropbox

www.dropbox.com