I am trying to pull information from one sheet to the next, however I am having trouble trying to make it show the number 6 if it is less then 6 for example...

If I pull information from worksheet Open1 Cell K7 I understand I do this using: =SUM('Open1'!K7)

If in Cell K7 the number is less then 6 I would like for it to show instead of a number less then 6 show the number 6, at the same time if the number is 6 or greater I would like for it to show that number without alteration. I tried messing with the IF function, but I kept getting numbers showing up in the thousands from a number that was 188...

I am sure I keep messing it up and its something simple..

  • The if() function should not have been returning values in the thousands - something is very wrong. While there are two solutions posted below that will solve your trouble, you should also post what you tried and we can tell you why it didn't work. In general you should always post what you tried - it gives people a starting point to help out from. – Adam Sep 8 '15 at 16:49

First of all: In your sample formula, there is no point in summing a single number.

=SUM('Open1'!K7) is absolutely identical to ='Open1'!K7 as long as 'Open1'!K7 is numeric. (If it is not numeric, then sum() will give an error.)

Now your request: You want to return the value in Open1'!K7 unless it is less than 6, in which case return 6? Banavalikar gave you one method. Here's another:


This compares the value in Open1'!K7 with the value 6, and returns whichever is greater.

(See also: min())


Try the following formula:

=IF(Open1!K7 < 6, 6, Open1!K7)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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