Formula or macro for value over X amount?

[wolf29]

Hi. I have spreadsheet where a value in a cell not be higher than 9999999.00. If so, I want to flag it which is easy enough with an IF statement, but is there a way to have tell me how many rows and of what values I need to have to still get the total but keep each row under the 9999999.00 threshold.

For example the cell may have a value of 12601045.33. So that's over the threshold we want to use and we are looking to somehow calculate for us that we need two rows with one being 9999999.00 and the other being 2601046.33. There may times where it may need to be more than two rows depending on the original value and how much it's over the threshold.

This is needed because this spreadsheet feeds a product job of ours where the field can only hold 9 bytes total (7 whole and 2 decimals) and wanted to see if there is a way to do this other than manually calculate what the breakdown needs to be.

Hope that makes sense.

Thanks,
Jay

 

[wolf29]

I think I was able to figure out how many rows of 9999999 I would need by using this formula:

=IF(I2>9999999,ROUNDDOWN(I2/9999999,0),"OK")

But would be great if I can get it to tell me what the remaining value should be after I would create the x amount of rows of 9999999.00.