PDA

View Full Version : Simple but not for me :(



didig
04-21-2013, 08:34 AM
I need a little help it is not a big thing but my level (unfortunately) is even lower than this...
what I'm trying to do is to put a value in a cell see to what of the 5 boxes it belongs and then put in the next cell the image correspoding to that box.

I prepared something directly in excel with more details on this and the file is attached here.

Thanks a lot in ADVANCE and congrats for the great forum:yes :clap:

SamT
04-21-2013, 10:17 AM
Instead of using images, which are hard to code for, use "Insert Symbol," which can be used with a simple "Copy" code. You can format a symbol like any other font for size and color.

didig
04-21-2013, 11:40 AM
Tnx for the suggestion i changed to symbol. now how to write the code :dunno

Tnx in advance :hi:

SamT
04-21-2013, 01:36 PM
Didig,

If you have 7 or fewer comparisons to make, a nested IF formula will work.

The Excel IF Function works like

=IF(Test1 [Comma] Result if true[Comma] result if false)

The first comma reads as "Then," and the second reads as "Else."

A Nested IF looks like

=IF(Test1[comma]Result1[comma]IF(test2[comma]Result2[comma]'')) Where the double quotes means the result is an empty string in the cell. You can also use an informational string inside the double quotes. For example if the number is greater than 22, you could return "Enter a number less than or equal to 22." If you don't use the double quotes, Excel will show some error message if all tests fail.

All your nested comparison formulas are basically the same

=IF(AND(CellA =>CellB,CellA<CellD),return(the symbol in) CellC, Next IF)
Since each preceeding comparison (Of all but the first) tests for a maximum, you don't have to use AND to check if this comparison is equal to or greater than.

=If(And(CellA =>CellB,CellA<CellD),CellC,IF(CellA<CellD,CellE,IF(CellF<CellG,CellH...
Should you use numbers or Cell References in the Formula?
If you often have to change the values to be compared against, use Cell References. If those numbers never or only rarely change, use numbers. Here after, I will use numbers. You can easily change them to Cell References in the Formula box by selecting ther number to change and, while it is selected, clicking in the Reference Cell.

First, in Cell "C7," enter the basic formula, ending it with double quotes to prevent errors.
=IF(AND(B7 =>4,B7<8.5),$J$2,"") The $ signs means that when you copy the formul down, it will always refer to J2.

Test the formula; Enter different values in "B7"

Replace the double quotes with the next comparison. You don't need to check if the value is greater than 8.5 because this comparison will never be evaluated if that were not true.

If(B7<13,$K$2,"") '(Note: no = sign.)
Now your C7 formula should read

=IF(AND(B7 =>4,B7<8.5),$J$2,If(B7<13,$K$2,""))
Test it.

Keep replacing the double quotes at the end of the formula with the remaining comparisons.

If(B7<17.5,$L$2,"")
If(B7<21,$M$2,"")
Your final formula will look like

=IF(AND(B7 =>4,B7<8.5),$J$2,If(B7<13,$K$2,If(B7<17.5,$L$2,If(B7<21,$M$2,""))))
But I would put "Over 22" in the final double quotes.

Test it with various values in B7 and when satisfied, Fill, (Select Fill Range, and CtrlD,) it down as far as needed

SamT
04-21-2013, 01:44 PM
PS: If you don't even want to have the symbol table on the sheet, you can copy each symbol and place it in the formula inside double quotes.

=If(And(B7 =>4,B7<8.5),"●",If(B7<13,"∆",If(B7<17.5,"□",If(B7<22,"○",""))))

didig
04-21-2013, 08:51 PM
Thanks a lot!! :clap2:

If i want to do this in VBA, so I also get a chance to improve, how can I do this?

THANKS :thumb