SOLVED

Excel Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2345925%22%20slang%3D%22en-US%22%3EExcel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2345925%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%20I%20need%20help%20trying%20to%20figure%20out%20a%20formula%20on%20my%20excel%20spreadsheet%20(attached%20below).%26nbsp%3B%20im%20trying%20to%20track%20a%20bi%20weekly%20value%20of%20fuel%20moistures.%26nbsp%3B%20once%20you%20open%20it%2C%20you%20will%20see%20on%20the%20Feb.%201%20tab%20i%20have%20entered%20my%20data%20under%20%22Gross%20Weight%22%20(highlighted%20in%20gray)%20towards%20the%20top.%20as%20i%20enter%20my%20data%20it%20gives%20me%20my%20%25%20in%20red%20towards%20the%20bottom%20(60%25%2C%2058%25%20and%2062%25).%26nbsp%3B%20This%20tab%20is%20working%20Fine.%26nbsp%3B%20Now%20click%20on%20the%20%22Feb.%2015%22%20Tab.%26nbsp%3B%20as%20you%20scroll%20towards%20the%20bottom%20right%20of%20the%20page%20you%20will%20see%20%E2%96%BC%2060%25%2C%20%E2%96%BC58%25%2C%20%26amp%3B%20%E2%96%BC%2062%25%20(IN%20RED).%26nbsp%3B%20so%20in%20those%20cells%20S34%2C%20V34%20%26amp%3B%20Y34%20i%20have%20formulas%20that%20aren't%20working%20as%20i%20would%20like.%26nbsp%3B%20i%20dont%20want%20anything%20showing%20up%20in%20Cells%20S34%2C%20V34%20%26amp%3B%20Y34%20until%20data%20is%20inputted%20in%20the%20%22Gross%20Weight%22%20(highlighted%20in%20gray)%20under%20%22Wet%22%20and%20%22Dry%22%20cells%20towards%20the%20top%20of%20the%20page.%26nbsp%3B%20i%20think%20the%20issue%20is%20that%20in%20cells%20X24%2C%20X26%20%26amp%3B%20X28%20i%20also%20have%20formulas%20which%20isnt%20showing%20those%20cells%20as%20blank.%26nbsp%3B%20but%20any%20help%20would%20be%20appreciated.%26nbsp%3B%20Thank%20You.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2345925%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346033%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346033%22%20slang%3D%22en-US%22%3EChange%20the%20formula%20in%20S34%20so%20it%20reads%20%3DIF(X24%3D0%2C%22%22%2CX24-'Feb.%201'!X24)%2C%20and%20make%20comparable%20changes%20in%20the%20adjacent%20cells%2C%20and%20you%20should%20be%20fine.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346056%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346056%22%20slang%3D%22en-US%22%3EThat%20Worked!%20thank%20you%20so%20much%20Sir.%20so%20just%20so%20i%20can%20learn%2C%20was%20my%20issue%20cause%20i%20was%20using%20the%20ISBLANK%20function%3F%20and%20because%20there%20was%20a%20formula%20in%20those%20cells%20it%20wasnt%20reading%20a%20true%20blank%20cell%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346058%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3EI%20changed%20the%20formulas%20on%20the%20Feb%201%20and%20Feb%2015%20sheets%3B%20I%20left%20the%20Mar%201%20sheet%20as%20it%20was.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346104%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346104%22%20slang%3D%22en-US%22%3EThank%20you%20Hans!%20that%20works%20as%20well.%20So%20im%20guessing%20but%20using%20the%20ISBLANK%20function%20was%20my%20issue.%20Can%20you%20explain%20that%20to%20me%20please%3F%20just%20trying%20to%20learn%20and%20understand%20why%20it%20didnt%20work%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346184%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EISBLANK%20only%20returns%20TRUE%20if%20the%20cell%20is%20really%20empty%2C%20i.e.%20it%20contains%20neither%20a%20constant%20value%20nor%20a%20formula.%20If%20the%20cell%20contains%20a%20formula%2C%20ISBLANK%20will%20return%20FALSE%20even%20if%20the%20formula%20returns%20an%20empty%20string%20%22%22%2C%20making%20the%20cell%20%3CEM%3Elook%3C%2FEM%3E%20blank.%3C%2FP%3E%0A%3CP%3EThe%20comparison%20%3CEM%3Ecell%3D%22%22%3C%2FEM%3E%20will%20return%20TRUE%20both%20if%20the%20cell%20is%20really%20empty%20and%20if%20the%20cell%20contains%20a%20formula%20that%20returns%20%22%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346572%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346572%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20Hans.%20Makes%20Sense%20now.%20really%20appreciate%20it!%3C%2FLINGO-BODY%3E
Contributor

Hi Everyone, I need help trying to figure out a formula on my excel spreadsheet (attached below).  im trying to track a bi weekly value of fuel moistures.  once you open it, you will see on the Feb. 1 tab i have entered my data under "Gross Weight" (highlighted in gray) towards the top. as i enter my data it gives me my % in red towards the bottom (60%, 58% and 62%).  This tab is working Fine.  Now click on the "Feb. 15" Tab.  as you scroll towards the bottom right of the page you will see ▼ 60%, ▼58%, & ▼ 62% (IN RED).  so in those cells S34, V34 & Y34 i have formulas that aren't working as i would like.  i dont want anything showing up in Cells S34, V34 & Y34 until data is inputted in the "Gross Weight" (highlighted in gray) under "Wet" and "Dry" cells towards the top of the page.  i think the issue is that in cells X24, X26 & X28 i also have formulas which isnt showing those cells as blank.  but any help would be appreciated.  Thank You.

11 Replies
best response confirmed by allyreckerman (Microsoft)
Solution
Change the formula in S34 so it reads =IF(X24=0,"",X24-'Feb. 1'!X24), and make comparable changes in the adjacent cells, and you should be fine.
That Worked! thank you so much Sir. so just so i can learn, was my issue cause i was using the ISBLANK function? and because there was a formula in those cells it wasnt reading a true blank cell?

@spalmer 

See the attached version.

I changed the formulas on the Feb 1 and Feb 15 sheets; I left the Mar 1 sheet as it was.

Thank you Hans! that works as well. So im guessing but using the ISBLANK function was my issue. Can you explain that to me please? just trying to learn and understand why it didnt work

@spalmer 

ISBLANK only returns TRUE if the cell is really empty, i.e. it contains neither a constant value nor a formula. If the cell contains a formula, ISBLANK will return FALSE even if the formula returns an empty string "", making the cell look blank.

The comparison cell="" will return TRUE both if the cell is really empty and if the cell contains a formula that returns "".

Thank you so much Hans. Makes Sense now. really appreciate it!
another question. If i wanted that formula to revert to a previous date/tab how would i do that. so just to explain it better. lets say missed to input data on the feb 15 tab, what would the formula look like for the next tab "Mar. 1" in those same cells?

@spalmer 

Can you explain in more detail what you want?

ya of course sorry about that. so lets say i inputted data in the "Feb. 1" Tab and wasnt able to collect data for the "Feb. 15" Tab but was able to collect data for the "Mar. 1" Tab. What would the formula look like if i wanted the cells S34, V34 & Y34 in "Mar. 1" Tab to revert to the "Feb. 1" Tab data incase i miss the data in the "Feb. 15" Tab.

Hopefully i explained that better if not please let me know

@spalmer 

In S34:

=IF(X24="","",X24-IF(COUNTA('Feb. 15'!N14:P19)=0,'Feb. 1'!X24,'Feb. 15'!X24))

Similar in V34 with X26, and in Y34 with X28.

Hans your are Awesome my friend!! thank you so much for your time and knowledge! i really appreciate it.