Excel Formulas

[Guest]

Please help me to create this formula:

A1 + A2 = B2

IF A1 + A2 = 2 THEN B2 = 2

IF A1 + A2 = 3 THEN B2 = 3

and so on

IF A1 + A2 = 10 THEN B2 = 1

IF A1 + A2 = 11 THEN B2 = 2

IF A1 + A2 = 12 THEN B2 = 3

IF A1 + A2 = 13 THEN B2 = 4

IF A1 + A2 = 14 THEN B2 = 5

IF A1 + A2 = 15 THEN B2 = 6

IF A1 + A2 = 16 THEN B2 = 7

IF A1 + A2 = 17 THEN B2 = 8

IF A1 + A2 = 18 THEN B2 = 9

IF A1 + A2 = 19 THEN B2 = 1

A1 and A2 must have a value between 1 and 9 otherwise ERROR!

 

[NC]

Thomas

the simplest way is to use data validation in the rows where you want
restrict the entries to be between 1 to 9. and then using the formula

left(A1+A2,1) to get the value between 1 & 9 as you want it.

Regards
NC

 

[NC]

sorry thomas
i mistakenly used "left" function instead of "right"
Regards
NC

 

[NC]

the correct formula without using any validations.sorry i misunderstood
the problem & sent some wrong messages initially

=IF(OR(OR(0>A1,0>A2),OR(A1>10,A2>10)),"Error",IF((A1+A2)=10,1,MOD((A1+A2),10)))
Regards
NC