need help with formula in pivot table or should i use vba?

NOS IF YOU'RE STILL OUT THERE PLEASE HELP!!!! desperate...

Hi again. Well Im back working where the inspiration for the workbook came from. I had some great help getting it to where it is because I knew nothing about pivot tables then and still don't today. 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.

I can't see a pivot table, nor a -8 in any O9s.

That is how noob i am. I had talked with NoS a member who actually made that for me, and he was saying it would be best with a pivot table so i assumed it was. To get the negative in 09 and create the errors you just have to fill out the form with like 20 barrels taken on just about any of the sheets. If one doesnt give you the negative number in column O then try a different sheet.

But I have actually made the formula work so it will not give a negative number. My problem now is I dont know how to make that formula copy down to the next row like it does without my edit and like all the other formulas do on every sheet. Any ideas??

Think you read more than what was written in #33 of your original thread.

Now using tables. Formulas will automatically fill in when needed.

Click to expand...

If you put a formula in O9, drag it down to the last line with data, or not drag it at all if there is no data yet, the table will then look after things for you.

Yeah, you're probably right, I read more. Good news though, I did solve the original problem of this thread. I also learned that I have to actually open the formula window to be able to have changes saved. I can't just to them in the formula bar or whatever its called. Idk if that's a glitch in my system, but that's how it is. When I make the change in the first cell of the column and click ok a little box pops up in the corner and when clicked on gives me an option to have the formula copied to all the cells in the column.
The
But with that fixed I have naturally found another #div/0 error that has totally stumped me. The problem is in the recovery inches column. That formula works fine. The problem is that I sometimes do and would always like to be able to put a measurement in without taking any barrels. Kind of an update I guess when a tank is slow filling and has a long way to go till next pull. As long as I only do it when there is a recovery inches amount it works fine. If I do it when there are 0 recovery inches I get #div/0 error in column N, 1" recovery (h:m). This is because the formula for that column is Lxx/Mxx, which when M is 0...#div/0. I know I need to nest a if() in there, like I did with the first problem but I'm not sure what the true value should be. I know the 1" recovery time needs to be the same. I thought of just putting =IF(M10="","",if(M10=0,N9,ML10/M10)). I believe that would work with everything M10 and below but what would that work in N9? You see there is no data in N9 because that is the first line of data. No calculations have been made.

Alter and/or adjust any and all formulas you deem necessary.

Make the adjustment in whatever row you need to, then drag the altered formula back up to line 9 so it will be applied to all the cells in that column.

ok i understand that, havent gotten to it yet to try but i will.

I do however have another small problem. I'm getting a number as text error in column d of all the sheets except for summary. Long story short, it seems all the sheets when it comes to formatting are somehow linked. When I format all the sheets column D to number so I dont get that error the summary sheet column D makes the date/time into a number. When I set summary sheet column D back to time/date format, I get the number as text error on all the other sheets. How do I unlink them so I can make summary sheet column D time/date and all the other sheets number. Also if you could explain why its doing that so if I see it again I will understand and be able to fix it myself.

thanks

Sorry Buds, I have no idea what you are talking about nor how you've gotten in that situation.

Nothing needed to have formatting changed. I believe on the tank site sheets all columns are formatted General except the ones for times and dates.

None of the sheets are linked.

haha this is getting crazy. I know im not the best at excel but this goes well beyond. The fix to the 0 in 1" recovery column that I put in every single sheet has gone away. I saved it several times after and have done nothing to EVERY SINGLE SHEET to put it back to the original formula. On top of that I can't even change the formula on any sheet to what was working without getting errors. Weird how it worked before but not now.

I also still have the same problem with the column D on every sheet. I put them all back to general and like clockwork summary sheet changes its format all by itself. This time I just selected the cells and not the column on summary sheet and changed them to time.date format. Still no luck. Im back to having every other sheet in D column tell me it is formatted at text or is preceded by an apostrophe. Im stumped.

All I want is to be able to put in 0 amount of barrels taken so I can randomly update the flow rate without getting the div/0 error. I'm thinking it should be possible. I would also like the format error fixed. I've tried everything I know. Is there anyone out there that can please help me with this???

thanks

ive attached the current version so you can see exactly whats going on. You might need to put some data in to see it. The userform button is on the summary sheet or ctrl-m from any sheet.

Time to abandon this, re-think things and start over.
It's now obvious the site tanks cannot be considered (even close to) being the same. (last Opal Holte entry, 5'4" difference)
Recovery times and amounts need to go back (possibly) multiple rows for calculation. (Zero barrels means continued recovery)
Many of existing columns can be eliminated by combining formulas. (or doing calculations at the userform level)

No! This is a great workbook. It does exactly what I wanted it to do. It just has a few areas that need some TLC. My boss gives me a list each day of what to go pull. I look at my summary sheet and say, "yeah I know". The workbook works. I can work around the errors. I only get them because I'm being anal about something. I'm the only one that uses this so its all good. I won't give up. I like the layout. I like the info (I'm looking at adding more details). I'll get the problems fixed, either by help or Google. I love Google.

Its funny that you picked one entry on one site to say that "its now obvious the site tanks cannot be considered (even close to) being the same." Did you look at the first 2 Opal Holte entries? How close are they? Did you look at the other 50 or so entries on the 15 sheets? I counted a total of 4 entries that were not close. 3 of which fixed themselves. Oh btw Opal Holte is broke down. Its not filling into one tank at all and I'm not able to pull from either. Things break...

If that sounded like a dig or I'm being an ass it was neither. You've done for me more than I could have ever expected. I thank you for that. This is the first time I've actually been about to use this workbook since you gave it to me. There are gonna be problems.

Anyway, thanks again for your time and help. I'm off to find another excel forum that might be able to help. I'm sure no one else here will want to after my little sissy fit. lol

thanks

mike

out

Have you ever checked your inbox?
Top right corner of page, under Notifications.

Sent a PM (personal message) couple months back.