Formula construction calculation question .

[Russ At Index]

Hi ,

Am stuck on a solution for the following problem . In column B i have assorted numbers 1 -99 for example . In column
A i have text , either 20 or 40.

I would like a formula which shows me the number greater than 20 which is in column B IF the word in column A says 40.
So , if row 1 column A had 40 as text and row 1 column B had 35 as number the answer i would like to see is 15 .

Any help greatly appreciated ,

Kind regards,

Russ.

 

[awoohaw]

Not sure I have your idea down correctly. What are the calculations whe A ="20" and B <40, What if A="20" and B>=40? And what happens when A = "40"? (How do you get 35 in your example?

This is what I gathered from your description of the scenario:

Mr excel questions 66.xlsm
	A	B	C
1
2	20	2	18
3	20	82	-62
4	20	31	-11
5	40	52	52
6	20	91	-71
7	40	56	56
8	40	39	39
9	40	76	76
10	20	75	-55
11	40	50	50
12	20	34	-14
13	40	77	77
14	20	67	-47
15	20	79	-59
16	40	53	53
17	20	53	-33
18	20	45	-25
19	20	82	-62
20	40	71	71
21	20	48	-28
22	40	66	66
23	20	62	-42
24	40	50	50
25	40	28	28
26	20	97	-77
27	40	59	59
28	40	80	80
29	40	47	47
30	20	29	-9
31	20	64	-44
32	40	55	55
33	40	35	35
34	40	88	88
35	40	2
36	40	52	52
37	40	31	31
38	40	43	43
39	20	6	14
40	40	92	92
41	20	45	-25
42	40	12
43	20	43	-23
44	20	43	-23
45	20	37	-17
46	40	24	24
47	20	27	-7
48	20	46	-26
49	20	94	-74
50	20	12	8
51	20	6	14
52	20	8	12
53	40	20
54	40	75	75
55	20	9	11
56	20	39	-19
57	40	23	23
58	20	61	-41
59	40	43	43
60	20	13	7
61	40	15
62	20	10	10
63	40	21	21
64	20	92	-72
65	40	51	51
66	40	73	73
67	40	71	71
68	20	62	-42
69	20	10	10
70	40	64	64
71	40	37	37
72	20	69	-49
73	40	5
74	40	97	97
75	40	93	93
76	40	58	58
77	20	88	-68
78	40	23	23
79	40	28	28
80	40	34	34
81	40	65	65
82	20	10	10
83	40	65	65
84	20	80	-60
85	20	34	-14
86	20	40	-20
87	40	41	41
88	20	62	-42
89	20	72	-52
90	20	46	-26
91	40	53	53
92	20	38	-18
93	40	35	35
94	20	67	-47
95	20	25	-5
96	20	94	-74
97	40	49	49
98	40	80	80
99	40	74	74
100	20	23	-3
Russ At Index

Cell Formulas
Range	Formula
C2:C100	C2	=IF(A2="40",IF(B2>20,B2,""),(1*A2)-B2)

 

[Russ At Index]

Hi Awoohaw ,

Thanks for the reply ,

Will re send data later , thanks again.

Russ