How to write a formula for the following condition ?

[RapchikM]

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

 

[RapchikM]

Yes, but can I ask why? The formula would be longer, require 6 functions instead of 3 & would not be applicable in any extra Excel versions so I can't see any logical reason to change.

Oh OK

but then would you still use LOOKUP also in 6 function with Index Match.

I explored bit on VLOOKUP against INDEX MATCH which one is rather better to use.
What i learnt is both the functions have its own benefits though Index Match would be faster and another advantage of INDEX MATCH against VLOOKUP ?

VLOOKUP will only work if the lookup value is in the first column. VLOOKUP cannot look to its left. However, INDEX MATCH solves this problem as it performs the lookup both horizontally and vertically. So, it doesn’t require the lookup value to be in the first column, it can be anywhere.

As in my case it seems the data is relatively smaller and therefore may not be required and no logical reason to change. You are a better judge

Thanks
RCM

 

[Peter_SSs]

In your case the lookup value is on the left so no problem there. I did some time testing of the formula I already gave you and the new one below and could find no discernible calculation time differences even over a fairly large range (several thousand rows) so I wouldn't change for that reason. For the record, here are the two versions. You can choose whichever one you want. I would stick with the LOOKUP version.

20 10 06.xlsm
	M	N	O	P
13		0	2.65
14		100	4.15	265
15		300	6.50	1095
16		500	8.00	2395
17
18	288	1045.20	1045.20
19	504	2427.00	2427.00
RapchikM (2)

Cell Formulas
Range	Formula
P14:P16	P14	=P13+(N14-N13)*O13
N18:N19	N18	=VLOOKUP(M18,N$13:P$16,3)+(M18-LOOKUP(M18,N$13:N$16))*VLOOKUP(M18,N$13:O$16,2)
O18:O19	O18	=INDEX(P$13:P$16,MATCH(M18,N$13:N$16))+(M18-INDEX(N$13:N$16,MATCH(M18,N$13:N$16)))*INDEX(O$13:O$16,MATCH(M18,N$13:N$16))