Day / Week / Month Rate - Formula

[SwampthingX90]

I'm trying to get the formula below to calculate a certain rate based on a certain date range. It's supposed to never exceed the monthly rate, but since I'm using 28 as a monthly divider, 10/3 thru 10/31 would end up at 25 days and use the weekly rate mixed with the daily rate which would exceed the monthly rate. Let me know if I need to expand further.

=IF(K16<>0,((H16*MOD(K16,7)*(MOD(K16,7)<4)+I16*((MOD(K16,7)>3)+INT(MOD(K16,28)/7))+J16*INT(K16/28))*D16),0)

 

[jasonb75]

@Shesil, I think you will find that your formula gives an incorrect result for the second row of the example.

 

[Shesil]

@SwampthingX90

Try the below one, its the same as earlier one but this time I have used your sheet cell reference and it is working:-

IF(K16>=28,INT(K16/28)*J16+INT(MOD(K16,28)/7)*I16+MOD(K16,7)*H16,INT(MOD(K16,28)/7)*I16+MOD(K16,7)*H16)

 

[jasonb75]

@Shesil, that is the same formula that you posted before. I tried it on my test sheet and it still returns the wrong result for the second row.

The incorrect result that I mention could be fixed by simply using =MIN(your formula, J16) but there could still be other possible scenarios where that will not work.
I presented a test sheet with a number of simple formulas that will be easier to fine tune in the event that any wrong results are found with further testing. Once it has been established that the results of the test are correct, those simple formulas can easily be combined into one for use in the full sheet.

 

[Shesil]

@jasonb75 , Maybe I didnt get exactly what is your point, but I am getting the same results of yours (4900,1360,4080,960) for all for four rows using my formula. I am new and not sure how to upload using xlbb sheet. Appreciate if you could assist me do that. I tried to download the XL2BB but it is not working.

 

[jasonb75]

Not sure what is different but when I put your formula into my sheet it gives 1615 for the second row.

With regard to XL2BB, did you unzip the file after downloading it? You will need to do that before you can complete the the steps to install the add-in to excel.
Also, if you want to move the extracted files to a different folder you will need to do that before installing it in excel.

If that doesn't help then I would suggest starting your own thread to try and get some assistance with XL2BB (if you haven't already done so) explaining what you have done so far and at which stage in the installation instructions it appears to have gone wrong.