New Grower Need help with grow spreadsheet formuli....

[TxOld Dude]

Any accounting or excel gurus out there? Need a little help verifying the formuli in my spreadsheet. Love what excel can do and I can do a lot, but the more complex it gets, the more I SUCK at it. The help section sucks for multiple embedded function formuli.

This test sheet is unlocked. The trouble is with the differential ppm and trying to get the proper difference and if < or = 0 then either "0" or blank. I think I've got it FINALLY, but need verification. This generates an "add-back" amount for nutes in ml and if it's wrong...
POOF! :firedevil:

In order, whereas...
IF measured ppm F11<=0 then "0" or blank (in G11). Differential ppm G11 =(target ppm $B$4*nute rate $B$5)-(measured ppm F11-base res ppm $B$3).

The formula:
=IF(F11<=0,"",PRODUCT(($B$4*$B$5)-(F11-$B$3)))

The sheet:
View attachment TEST sheet GH Lucas Log.xls

Thanks!!!

Fish

 

[TxOld Dude]

bumpin' it... sorry... need this

 

[wwwillie]

Sorry man, my excel skills are rudimentary at best. Can network stuff like a sumbitch though!

 

[AndyBotwin]

Hey Fish, I'm a programmer and I also do a lot of complicated Excel work so I looked this over. Your conditional logic is correct for the IF statement, and all the results all work out properly. Nice job!

I reverse tested it by leaving the formulas in but removing the IF statement, and if that happens it will treat the measured ppm as 0 and show a differential ppm of 790, micro add of 14.3, and bloom add of 28.6 (max values). Putting the IF statement back kills all calculations if a 0 or blank is in place in the measured ppm cell. The only thing you are missing is validation to not run the calculation if anything other than a number is entered in that cell, but to do that you'd need to write it in VBA code. As it stands now, if an alpha character gets entered it all errors out, so it's easy enough to see there's a problem.

 

[Eekman]

I would like to see how this turns out

Keep us updated please:Sharing One:

Peace

Eek

 

[TxOld Dude]

Hey Fish, I'm a programmer and I also do a lot of complicated Excel work so I looked this over. Your conditional logic is correct for the IF statement, and all the results all work out properly. Nice job!

I reverse tested it by leaving the formulas in but removing the IF statement, and if that happens it will treat the measured ppm as 0 and show a differential ppm of 790, micro add of 14.3, and bloom add of 28.6 (max values). Putting the IF statement back kills all calculations if a 0 or blank is in place in the measured ppm cell. The only thing you are missing is validation to not run the calculation if anything other than a number is entered in that cell, but to do that you'd need to write it in VBA code. As it stands now, if an alpha character gets entered it all errors out, so it's easy enough to see there's a problem.

OUTSTANDING!!!!! :grat:

Many, many thanks for that Andy! I'm making this available on my thread (see signature link) to replace a previous version that is flawed. I'm also adding it as a tab in another Nutrient Profiler workbook. The max nute under manual calculations using the Lucas formula comes out to 24/48, so that's real close. As you saw, this for calculations on the fly that take into account the ppm of the res water with all the "extras" added before nutes based on the res size and nute rate.
Max Reps Dude!!!!



Master Eekman it should be available in the next post to my grow thread shortly!

Fish

 

[TxOld Dude]

OOPS!! Nope. This portion of the function still won't operate. (F11-$B$3) should subtract the res base ppm from the measured ppm.
If I set the nute rate at "1" and the measured ppm at 650, I should get 650 not 777...
($B$4 1300*$B$5 1)-(F11 650-$B$3 127) =650

If I do this:
($B$4 1300*$B$5 1)-(F11 1300-$B$3 127) I get =127 and should be "0"
Or this:
($B$4 1300*$B$5 1)-(F11 0-$B$3 127) I get =1427 and should be "1300"
makes no sense as the res base ppm must be removed from the measured ppm before the final answer. :dunno:

Suggestions for a rearrangement, if any?

Differential (should read "variance" to be accurate) = Target ppm x nute rate minus measured ppm minus res base ppm

 

[AndyBotwin]

Keeping in mind that I'm a soil grower and unfamiliar with these hydro formulas, I'm not understanding your math here: ($B$4 1300*$B$5 1)-(F11 650-$B$3 127) you say should be 650. But this formula breaks down to 1300-523, which is 777 as the spreadsheet shows. It's removing the res base ppm just like you're asking it to. To get the answers you want would require a formula of (target ppm x nute rate) - measured ppm. I guess I just don't get why the res base ppm is coming into play at all. If the variance is between what you just measured and the target rate, what does it matter what the base used to be? We have a new measurement now and the base is already included in it. Right?

I've looked around at a bunch of sites and they all use this formula: ((target - current) / target) * 8 ml per gallon * res gallons = Micro (ml) - double this figure to get Flora Bloom (ml)

That works in your spreadsheet if you remove the piece about base ppm so you have this for your G11 column: =IF(F11<=0,"",PRODUCT(($B$4*$B$5)-(F11))). It works properly from what I can see using a target off 1300 and a nute rate of 1. If I put in a measured ppm of 1, it tells you to add 24/48 which is the max since the nutes are basically all gone. If you put in 1300, it tells you to add 0 since we are measuring right where we want to be. Throwing the base ppm into the mix messes up the numbers and I'm not sure why you're doing that.

Sorry if I'm missing something here!

 

[pop22]

I like my formula, read the direction, reduce by half, add to res.............LOL!

 

[TxOld Dude]

Keeping in mind that I'm a soil grower and unfamiliar with these hydro formulas, I'm not understanding your math here: ($B$4 1300*$B$5 1)-(F11 650-$B$3 127) you say should be 650. But this formula breaks down to 1300-523, which is 777 as the spreadsheet shows. It's removing the res base ppm just like you're asking it to. To get the answers you want would require a formula of (target ppm x nute rate) - measured ppm. I guess I just don't get why the res base ppm is coming into play at all. If the variance is between what you just measured and the target rate, what does it matter what the base used to be? We have a new measurement now and the base is already included in it. Right?

I've looked around at a bunch of sites and they all use this formula: ((target - current) / target) * 8 ml per gallon * res gallons = Micro (ml) - double this figure to get Flora Bloom (ml)

That works in your spreadsheet if you remove the piece about base ppm so you have this for your G11 column: =IF(F11<=0,"",PRODUCT(($B$4*$B$5)-(F11))). It works properly from what I can see using a target off 1300 and a nute rate of 1. If I put in a measured ppm of 1, it tells you to add 24/48 which is the max since the nutes are basically all gone. If you put in 1300, it tells you to add 0 since we are measuring right where we want to be. Throwing the base ppm into the mix messes up the numbers and I'm not sure why you're doing that.

Sorry if I'm missing something here!

Yeah your right about that : 523. Sorry, head not on straight last night! Yes, the 1300 is max nutes, nothing else. So the 523 would be correct not the 650, I see your point. My Bad! This sinus infection got my head f'd up. Sorry about that!

Re: base ppm: BUT, because the measured includes everything: nutes and all. The res actual at full strength would be 1427. All the additives (other than nutes) and water impurities (127) must be removed from the measured ppm so that just the accurate nute portion of the measured is used to calculate the add-back. The formula listedin the Lucas posts assumes water ppm of "0" and no additives (calmag, Zone, tea, etc.) and in real world, all that must be compensated for to get an accurate rate even though a little of the base ppm is used by the plant.

Thanks Bro!