Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using Microsoft Office 365. I'd like to do something like

=(SUM(J4:J20)+K23)/G23

but sometimes K23 doesn't have a value. So I'm thinking I need to say only do +K23 if it contains a value.

How would I do that?

share|improve this question
    
@slhck Thank you for showing me how to better ask my question. –  David Custer Jul 25 '13 at 9:54
    
Do you want the division by G23 to apply to the sum of J4:J20 if K23 has no value? I know that's not what the formula says, just checking –  Raystafarian Jul 25 '13 at 12:34
    
@Raystafarian Yes –  David Custer Jul 25 '13 at 16:37
    
You should change your requested formula to indicate that like =(sum(J:J)+K)/G) –  Raystafarian Jul 25 '13 at 16:47
    
@Raystafarian I have done so. Thank You! –  David Custer Jul 25 '13 at 17:01

4 Answers 4

up vote 1 down vote accepted

Try using

=SUM(J4:J20,IF(K23<>"",K23))/G23

Unless sometimes G23 is blank or zero, replace /G23 with /if(G23<>"",G23,1)

share|improve this answer
    
Right now I'm using =SUM(J4:J20,IF(K23<>"",K23))/if(G23<>"",G23,1) ... Is it possible to say if G23 is blank or 0 then abort/don't do anything/exit/don't even do =SUM ?? –  David Custer Jul 25 '13 at 17:45

With Office 365 you could use IFError() like

=SUM(J4:J20)+IFERROR(K23/G23,0)

ISError() works differently and the earlier suggestion will present an error. You can still use ISError(), but it would need to be nested in an IF() statement:

=SUM(J4:J20)+IF(ISERROR(K23/G23),0,K23/G23)

This will mean that the division is carried out twice, which is slower than the IFError(), where it is only calculated once.

share|improve this answer
    
I'm looking for (J4:J20) which is $17.20 + $23.70 + $78.00 then plus K23, if there is a value (which in this case is $20), then divide it all by G23 which is 130.... So using my calculator that should be $1.07. And the formula your giving me comes up with $119.05? –  David Custer Jul 25 '13 at 17:18
    
Ah, I started with the suggested IsError formula, which had the concept wrong. Never mind. –  teylyn Jul 25 '13 at 21:17
    
Thank You for your help! –  David Custer Jul 25 '13 at 21:30

There's no need to use any IF conditions here. Just include K23 in the SUM function. SUM will ignore any non-numeric, non-error values, so it doesn't matter if K23 is blank.

 =SUM(J4:J20,K23)/G23
share|improve this answer

=if(k23=0,"", function)

the first part of an if function is the logical test (i.e. is a certain cell blank)

the second part (after the first comma) says to leave answer blank (notice, there's nothing between the quote marks)

the third part (after the second comma) is where you type your normal function...

share|improve this answer
2  
This doesn't look like a complete answer... Can you expand on it? –  Canadian Luke Jul 2 at 20:06
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Carl B Jul 2 at 23:35
    
opps! I had it wrong at first! updated answer should work!! –  bryaninthesky Jul 2 at 23:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.