Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Formulas
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! -- Thomas |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
sorry thomas
i mistakenly used "left" function instead of "right" Regards NC |
#4
|
|||
|
|||
the correct formula without using any validations.sorry i misunderstood
the problem & sent some wrong messages initially =IF(OR(OR(0A1,0A2),OR(A110,A210)),"Error",IF(( A1+A2)=10,1,MOD((A1+A2),10))) Regards NC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 2002 - copying formulas across worksheets | Excel Discussion (Misc queries) | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
How do I get to master Excel functions and formulas? | Excel Worksheet Functions |