I'm trying to subtract 900 (C21) from the next smallest value. How would I do this? My values are
C8 547
C9 769
C10 855
C11 922
C12 1024
C13-19 blank data fields
So it would be 900-855. I only want it to show up if it gives a positive answer. How would I write this in a formula?
This is what I have so far but obviously I've done something wrong
=C21-(SMALL($C$8:$C$19,1+COUNTIF($C$8:$C$19,SMALL($C$8:$C$19,-1))))
Hi Wilson and welcome to the forum,
Try this..
=900-VLOOKUP(900,$C$8:$C$12,1,TRUE)
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks Marvin that's what I was looking for!
One more issue I had is if C21 is blank the formula gives me an answer of "#N/A". How would I get the formula to output "0" instead?
=C21-VLOOKUP(C21,$C$8:$C$19,1,TRUE)
=c21-small(c8:c18,countif(c8:c18,"<"&c21))
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
FormulaFormula:
Please Login or Register to view this content.
Or try this: =C21-MAX(IF(C8:C19<C21,C8:C19,""))
Enter with Ctrl+Shift+Enter
If the formula that MarvinP gave you works correctly and all you want is a 0 if C21 is blank, this will do it.
Formula:Please Login or Register to view this content.
I substituted 1 for TRUE just to shorten the formula a wee bit. The IFERROR returns the 0 at the end of the formula if C21 is blank.
You also mention another requirement of having answers show if greater than 0 and no error values if C21 is blank.
I think that this meets all the requirements that you have mentionned.
Formula:Please Login or Register to view this content.
If C21 is blank, a blank is returned
If C21's value produces a negative value then 0 is returned
If C21's value produces a positive value, that value is returned.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks