Previous Topic Next Topic

FORMULA QUESTION???

Closed
sloblox - Oct 21, 2010 at 09:33 AM
 BHATT - Oct 30, 2010 at 11:42 PM
Hello,

I have five cells , each with a numeric value ...... and i have another cell which allows the input of a number , i am looking for a formula that will compare the inputted value with the other five values and if there is a match then "positive" if there is no match then "negative"

help please.


4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 21, 2010 at 10:36 AM
Hi Sloblox,

Use nested IF's.

Your five cells have range A1:A5.
Your input cell is B1.
Then C1 or whatever cell: 
=IF(B1=A1,"positive",IF(B1=A2,"positive",IF(B1=A3,"positive",IF(B1=A4,"positive",IF(B1=A5,"positive","negative")

Helpfull?

Best regards,
Trowa
0
sloblox
Oct 21, 2010 at 01:30 PM
Its nearly there !! but what i need to achieve is .....if b1 = any one of the five numbers from a1 to a5 then that would give a "positive" but if b1 = anything else other than any of the five numbers in a1 to a5 then it would produce a "negative"
hope i have explained that better this time ... thanks
0
Answer 2 / 4
RayH
Oct 21, 2010 at 03:41 PM
=IF(B1=A1,"Positive",IF(B1=A2,"Positive",IF(B1=A3,"Positive",IF(B1=A4,"Positive",IF(B1=A5,"Positive","Negative")))))
0
RayH
Oct 21, 2010 at 03:43 PM
Just noticed this is the same as TrowaD. THis does do what you are asking for.
0
sloblox
Oct 22, 2010 at 02:12 PM
i have 5 numbers (they are actually 5 different lengths of timber) 2.4,3.0,3.6,4.2 & 4.8 what i am trying to do is get excel to calculate from a number that is input by the user , which of the 5 different lengths would be the most suitable......ie if you entered 2.4 as your length , then obviously excel would select 2.4, but if you entered 3.3 , then the closest number greater than 3.3 would be 3.6 , in addition to this , is it possible for excel to calculate from the number input if it is much bigger using multiples of the 5 lengths available.....ie lets say you input 5.4 , then a combination of 2.4 & 3.0 would be ideal , is this possible.
0
RayH
Oct 22, 2010 at 04:08 PM
I'd say you're going need to use VBA to do all that.
0
Answer 3 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 26, 2010 at 09:37 AM
Hi Sloblox,

This is a totally different question than what you started out with!

Try this formula: 
=IF(CEILING(B1,A1)=A1,A1,IF(CEILING(B1,A2)=A2,A2,IF(CEILING(B1,A3)=A3,A3,IF(CEILING(B1,A4)=A4,A4,IF(CEILING(B1,A5)=A5,A5,"Use multiple sizes")))))

When you enter 3.3 in cell B1 the result will be 3.6, since this is the bigger number that is closest to the input number.
When you enter a number that is bigger then the number entered in A5 the text "Use multiple sizes" will be visible.

Sorry, but I have no idea how to look for a combination of numbers to come close to the input number.

Hopefully this formula will be of some use for you.

Best regards,
Trowa
0
Answer 4 / 4
BHATT
Oct 30, 2010 at 11:42 PM
you can use the following logic also:

Input in A1

Values to be compared in A2,B2,C2,D2,E2

=IF(OR(A1=A2, A1=B2, A1=C2, A1=D2, A1=E2),1,0)
0

Similar discussions

Keyboard troubles
jimbobjoe -
car -

38 responses
My account locked connect to gemail
Karthik -
winko -

2 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response