Hello
Can anyone help me to write the formula for below condition ?
Fixed Range
Cell N13 = 000-100 Utilized Numbers
Cell N14 = 101-300 Utilized Numbers
Cell N15 = 301-500 Utilized Numbers
Cell N16 = Above 500 Utilized Numbers
Cell O13 = 2.65
Cell O14 = 4.15
Cell O15 = 6.50
Cell O16 = 8.00
Cell M18 = 288
The formula i want to create is in cell N18 on basis of Cell M18 which has value of 288 Which should calculate no of Utilized Numbers of Cell N13 which completely utilizes number = 100
as it falls in the range from 000-100 Full Utilized Numbers and then it should select from Cell N14 which value = 188 as it falls in the range of 101-300 so Utilized Numbers =200 and 188 is less than 200 in cell N14
Therfore it should calculate
N18 = (100 * $O$13) + (188*$0$14)
So 288 broken into 100 and 188
cell M19 = 504
N19 = (100 * $O$13) + (200*$0$14)+(200*$0$15)+(4*$0$16)
therfore 504 is broken into 100+200+200+4
f cell M20 = 2000
N20 = (100 * $O$13) + (200*$0$14)+(200*$0$15)+(500*$0$16)+(500*$0$16)+(500*$0$16)
therfore 2000 is broken into 100+200+200+500+500+500
Shall be really thankful for your valuable suggestion
RCM
Can anyone help me to write the formula for below condition ?
Fixed Range
Cell N13 = 000-100 Utilized Numbers
Cell N14 = 101-300 Utilized Numbers
Cell N15 = 301-500 Utilized Numbers
Cell N16 = Above 500 Utilized Numbers
Cell O13 = 2.65
Cell O14 = 4.15
Cell O15 = 6.50
Cell O16 = 8.00
Cell M18 = 288
The formula i want to create is in cell N18 on basis of Cell M18 which has value of 288 Which should calculate no of Utilized Numbers of Cell N13 which completely utilizes number = 100
as it falls in the range from 000-100 Full Utilized Numbers and then it should select from Cell N14 which value = 188 as it falls in the range of 101-300 so Utilized Numbers =200 and 188 is less than 200 in cell N14
Therfore it should calculate
N18 = (100 * $O$13) + (188*$0$14)
So 288 broken into 100 and 188
cell M19 = 504
N19 = (100 * $O$13) + (200*$0$14)+(200*$0$15)+(4*$0$16)
therfore 504 is broken into 100+200+200+4
f cell M20 = 2000
N20 = (100 * $O$13) + (200*$0$14)+(200*$0$15)+(500*$0$16)+(500*$0$16)+(500*$0$16)
therfore 2000 is broken into 100+200+200+500+500+500
Shall be really thankful for your valuable suggestion
RCM