Another Excel Help
Karthik Bhat
help me please to modify the formuale.....
i have a formulae which outputs the following text for an input number ..
Input No............ .........TEXT OUTPUT
0 to 17 ..........................3 C X 1.5 mm2
18 to 24 ............................3 C X 2.5 mm2
25 to30 ..........................3 C X 4.0 mm2
31 to39 ..........................3 C X 6.0 mm2
40 to52.......................... 3 C X 10 mm2
53 to 66.......................... 3 C X 16 mm2
67 to 90 ..........................3 C X 25 mm2
91 to 110 ..........................3 C X 35 mm2
111 to 135 ..........................3 C X 50 mm2
136 to 165 ..........................3 C X 70 mm2
166 to 200..........................3 C X 95 mm2
200 to 1000...............calculate...
can i modify the formula to change the output for No inputs from "200 to 1000"
i want to change the output text "calculate" to some other text using
the below logic.......
1)if input number is greater than 200 divide input No by 2 & o/p text
from above values preceding by "2 x ".....
example....
Imput No.........................output text
202................................2 x 3 C X 35 mm2(as 202/2=101)
2)IF input No by 2 is greater than 200, then divide input No by 3 &
o/p text from above values preceding by "3 x "
example....
Imput No.........................output text
412................................3 x 3 C X 35 mm2(as 412/3=104)
3)IF input No by 3 is greater than 200, then divide input No by 4 &
o/p text from above values preceding by "4 x "
example....
Imput No.........................output text
608................................4 x 3 C X 70 mm2(as 608/4=152)
4)IF input No by 4 is greater than 200, then divide input No by 5 &
o/p text from above values preceding by "5 x "
example....
Imput No.........................output text
805................................3 x 3 C X 70 mm2(as 805/5=161)
5)IF input No by 5 is greater than 200........
continue with the above logic or output text "calculate"....
=CONCATENATE(IF(A1>=201,"CALCULATE",IF(AND(A1<201,A1>=136)," 3 C X 95
mm2",IF(AND(A1<136,A1>=136)," 3 C X 70 mm2",IF(AND(A1<176,A1>=111)," 3
C X 50 mm2",IF(AND(A1<111,A1>=91)," 3 C X 35
mm2",IF(AND(A1<91,A1>=67)," 3 C X 25
mm2","")))))),IF(AND(A1<67,A1>=53)," 3 C X 16
mm2",IF(AND(A1<53,A1>=40)," 3 C X 10 mm2",IF(AND(A1<40,A1>=31)," 3 C X
6 mm2",IF(AND(A1<31,A1>=0)," 3 C X 4 mm2"," ")))))
.
.
.
any other formuale would do provided i get to know the logic and
procedure of using that formuale....,.
thanks in advance...
--
Thanks
Karthik Bhat
Microsoft MVP