First I dont see where or how to upload or link my workbook. If someone could please tell me how I will do so right away, otherwise this whole thread is pointless. lol
This is a little long so please bare through it. I could really use some help/ideas for a couple things I want to be able to do but cant due to !div/0 errors. This was written for me by someone else with a lot more excel knowledge than I have.
With that said...
Im hoping someone can take a look at this and help with what i think and hope is an easy solution.
I basically got the version of the workbook the same time i was pulled off the job and havnt been able to get some real time data in to test it out. Well I can now and Ive found one thing that im not sure how to fix because its in a pivot table.
The best place to look for what im going to try to explain is on the Opal Holte sheet. The problem lies in O9 where there is a -8. Technically the -8 is correct but as you see the other formulas can not handle negative numbers.
Ok, now I will hopefully explain this so you understand whats going on in this workbook ...
The goal of this workbook is to give me the most accurate time to pull from a set of tanks by using date/time, levels and amount of water pulled to determine the approximate fill rate of the tanks.
I pull water from 20 ft tanks. Every tank I pull from is listed on the summary page. Every other page are those tanks with more details and where the userform inputs the data to be calculated.
Now some geeky water tank/er info. I pull in quantities called barrels (I'm actually in the oil field biz, thats why its barrels). The water tank i pull from hold 20 feet of water. Every foot of water is 20 barrels. If i pull 160 barrles the water tank drops to 12 feet (assuming it was full at 20 ft). Now the tricky part...when i pull it comes from 2 water tanks at the same time. Assuming again both water tanks are full at 20 feet and i pull 160 barrles (8 ft of water) it would take 4 ft from each tank, therefore each tank would be at 16 ft. Both water tanks also fill at the same time so they are usually always at the same height (within a few inches), forsaking any malfunctions in either.
Now the formulas...and the problem. To get some of the formulas to work everything needed to be converted into inches. That is why you see columns with feet and inches then you see some with just inches. You can also see barrels taken in "barrel" numbers (150) and barrels(bbls) taken in inches (20 bbls = 12 in. 150/20*12/2 = 45...the /2 at the end is because I pull from 2 tanks at a time).
There are a couple formulas in there that convert it from ft/in to in, and another that converts it back from inches to ft/in. This is because I have to report every pull to my boss in ft/in.
Each row is a new pull i have made.
The time dif column the the length of time between the last pull and the one i just did.
The recovery inches is how many inches of water have filled the tank between the last and current pull. This is based off tank 1 since both tanks are generally the same height.
1" recovery column is the time it took to fill 1" of water. In that formula you see 62. This number is based off of no pull will be less than 4 ft (48 in) and I can only pull down to 1'2" (14") remaining in the tank (fail designers, drain pipe to high). 48" + 14" = 62". This too is based off tank 1. Same reason.
Recovery Needed Inches column is how many more inches are needed before a pull can be made.
Estimated time to full pull column. That is how long till, H:M till next pull should be ready.
SO THAT'S IT IN A NUTSHELL. The problem lies in 09 where you have inches needed. The reason there is a negative number there is because the formula is based on the tanks always being low enough to only need one pull. That is not the case. The sheet I sent you to for the example had to be pulled from 3 times for this workbook to start working right.
I need that formula to be able to calculate every 48" above 62" and know that when its at 11 ft, for example, that it can be pulled from 2 times when pulling 160 bbls (barrels).
Im sorry for going on so long with this. I just wanted you to understand how it works. I hope you do understand and im sure i went on way too long, I know you are all smart enough to understand.
Thank you for you time and help. Hope to hear from someone soon.
This is a little long so please bare through it. I could really use some help/ideas for a couple things I want to be able to do but cant due to !div/0 errors. This was written for me by someone else with a lot more excel knowledge than I have.
With that said...
Im hoping someone can take a look at this and help with what i think and hope is an easy solution.
I basically got the version of the workbook the same time i was pulled off the job and havnt been able to get some real time data in to test it out. Well I can now and Ive found one thing that im not sure how to fix because its in a pivot table.
The best place to look for what im going to try to explain is on the Opal Holte sheet. The problem lies in O9 where there is a -8. Technically the -8 is correct but as you see the other formulas can not handle negative numbers.
Ok, now I will hopefully explain this so you understand whats going on in this workbook ...
The goal of this workbook is to give me the most accurate time to pull from a set of tanks by using date/time, levels and amount of water pulled to determine the approximate fill rate of the tanks.
I pull water from 20 ft tanks. Every tank I pull from is listed on the summary page. Every other page are those tanks with more details and where the userform inputs the data to be calculated.
Now some geeky water tank/er info. I pull in quantities called barrels (I'm actually in the oil field biz, thats why its barrels). The water tank i pull from hold 20 feet of water. Every foot of water is 20 barrels. If i pull 160 barrles the water tank drops to 12 feet (assuming it was full at 20 ft). Now the tricky part...when i pull it comes from 2 water tanks at the same time. Assuming again both water tanks are full at 20 feet and i pull 160 barrles (8 ft of water) it would take 4 ft from each tank, therefore each tank would be at 16 ft. Both water tanks also fill at the same time so they are usually always at the same height (within a few inches), forsaking any malfunctions in either.
Now the formulas...and the problem. To get some of the formulas to work everything needed to be converted into inches. That is why you see columns with feet and inches then you see some with just inches. You can also see barrels taken in "barrel" numbers (150) and barrels(bbls) taken in inches (20 bbls = 12 in. 150/20*12/2 = 45...the /2 at the end is because I pull from 2 tanks at a time).
There are a couple formulas in there that convert it from ft/in to in, and another that converts it back from inches to ft/in. This is because I have to report every pull to my boss in ft/in.
Each row is a new pull i have made.
The time dif column the the length of time between the last pull and the one i just did.
The recovery inches is how many inches of water have filled the tank between the last and current pull. This is based off tank 1 since both tanks are generally the same height.
1" recovery column is the time it took to fill 1" of water. In that formula you see 62. This number is based off of no pull will be less than 4 ft (48 in) and I can only pull down to 1'2" (14") remaining in the tank (fail designers, drain pipe to high). 48" + 14" = 62". This too is based off tank 1. Same reason.
Recovery Needed Inches column is how many more inches are needed before a pull can be made.
Estimated time to full pull column. That is how long till, H:M till next pull should be ready.
SO THAT'S IT IN A NUTSHELL. The problem lies in 09 where you have inches needed. The reason there is a negative number there is because the formula is based on the tanks always being low enough to only need one pull. That is not the case. The sheet I sent you to for the example had to be pulled from 3 times for this workbook to start working right.
I need that formula to be able to calculate every 48" above 62" and know that when its at 11 ft, for example, that it can be pulled from 2 times when pulling 160 bbls (barrels).
Im sorry for going on so long with this. I just wanted you to understand how it works. I hope you do understand and im sure i went on way too long, I know you are all smart enough to understand.
Thank you for you time and help. Hope to hear from someone soon.
