SOLVED

Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1571033%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571033%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20i%20am%20looking%20for%20a%20formula%20for%20the%20attached%20spreadsheet%20in%20column%20S%20that%20will%20only%20add%20columns%20K%20through%20Q%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20there%20is%20something%20in%20column%20Q%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20it%20would%20be%20easier%20to%20only%20total%20column%20R%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20there%20is%20something%20in%20column%20Q......%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebasically%20I%20don't%20want%20%3CU%3Eany%3C%2FU%3E%20average%20figure%20until%20column%20Q%2Fmonth%206%20has%20an%20entry%20in%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20can't%20work%20it%20out!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20great%2C%20thank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1571033%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571117%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751614%22%20target%3D%22_blank%22%3E%40Pamsie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20will%20keep%20Cell%20S4%20blank%20until%20something%20is%20in%20Q4.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(Q4%26lt%3B%26gt%3B%22%22%2C%20R4%2F7%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EYou'll%20probably%20want%20to%20do%20something%20similar%20in%20columns%20T%20through%20V%2C%20just%20to%20keep%20them%20blank%20until%20S%20has%20something%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20generally%20found%20in%20my%20own%20sheets%2C%20that%20the%26nbsp%3B%20%3CSTRONG%3E%26lt%3B%26gt%3B%22%22%3C%2FSTRONG%3E%20is%20a%20good%20way%20to%20say%20%22not%20blank%22%26nbsp%3B%26nbsp%3B%26nbsp%3B%20It%20could%20also%20be%20turned%20around%2C%20%3CSTRONG%3E%3DIF(Q4%3D%22%22%2C%22%22%2C%20R4%2F7)%3C%2FSTRONG%3E%2C%20which%20is%20then%20saying%20IF%20Q4%20is%20blank%2C%20keep%20S4%20blank%3B%20otherwise%2C%20divide%20R7%20by%207.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571120%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751614%22%20target%3D%22_blank%22%3E%40Pamsie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(K4%3AQ4)*(Q4%26lt%3B%26gt%3B%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20R4%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571133%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571133%22%20slang%3D%22en-US%22%3E%3CP%3EOh%20Sergei%2C%20that%20has%20worked%20BEAUTIFULLY!!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20even%20think%20of%20'not%20blank'%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eabsolutely%20wonderful%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1573865%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1573865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F751614%22%20target%3D%22_blank%22%3E%40Pamsie%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, i am looking for a formula for the attached spreadsheet in column S that will only add columns K through Q IF there is something in column Q

 

Perhaps it would be easier to only total column R IF there is something in column Q......

 

basically I don't want any average figure until column Q/month 6 has an entry in it. 

 

I just can't work it out!!!

 

Any help would be great, thank you  

4 Replies
Best Response confirmed by Pamsie (New Contributor)
Solution

@Pamsie 

 

This formula will keep Cell S4 blank until something is in Q4.

=IF(Q4<>"", R4/7,"")

You'll probably want to do something similar in columns T through V, just to keep them blank until S has something in it.

 

I've generally found in my own sheets, that the  <>"" is a good way to say "not blank"    It could also be turned around, =IF(Q4="","", R4/7), which is then saying IF Q4 is blank, keep S4 blank; otherwise, divide R7 by 7.

 

@Pamsie 

Perhaps you mean

=SUM(K4:Q4)*(Q4<>"")

in R4

Oh Sergei, that has worked BEAUTIFULLY!!!!

 

thank you!

 

I didn't even think of 'not blank'   

 

absolutely wonderful  @mathetes 

@Pamsie , you are welcome