Testing for values

[elad]

Hi!

Have an excelsheet with many colums with different values. If the value is between a range I would like to dislpay a text in a new colum. Excample below:


Test value result
10 to 19 "A"
20 to 29 "B"
30 to 39 "C"

etc..etc.


best regards
Elad

[JohnTopley]

Create table with 10,20,30 etc in Column A and A,B,C etc in column B

with "lookup" value in C1

in D1

=IFERROR(INDEX($B$1:$B$100,MATCH($C$1,$A$1:$A$100,1)),"Below limit")

any value < 10 will give "Below limit" message: change as require

[elad]

Hi -thanks for quick replay!

It seems like it is working Thanks a lot!

Best regards
Elad

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[elad]

One question left.... It seems that the values in Colum C, wich in my sheet already are there, has to be entered again before it change the value in Colum D?

F9 does not help, and also checked that formula is set to atutomatic update.....

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JohnTopley]

If "C" is a data column i.e your values are in C1,C2 etc then id D1

=IFERROR(INDEX($B$1:$B$100,MATCH($C1,$A$1:$A$100,1)),"Below limit")

Copy down

[elad]

The formula working fine, but it only works after I enter the number in colum C (D in testsheet).

In the test sheet, the colum is different, but I guess you will understand that

Regards
Elaf

[JohnTopley]

in D2

=IF(ISNUMBER(MID($C2,FIND(".",$C2)+1,2)+0),MID($C2,FIND(".",$C2)+1,2)+0,0)

assumes numbers are always 2 digits

[elad]

Ok maybe - but how will that give the correct text in E2, and what is the different as long as the value in D2 is correct?

Regards
Asbjørn

[JohnTopley]

Have you actually tried the above????

[elad]

Yes.... se attachment. And it is also good to know why and not only just copying the formula so I can learn from it

Regards
Elad

[JohnTopley]

See attached: formula converts the TEXT to NUMBER/GENERAL format so you can match with your table.

[elad]

Perfect!

If there is no .xx (ie. C:\ProgramData\Hundegger\SC\P18-0011.bvx) could it display the text for "up to 10". In my case the text "Takstol"?

Best regards
Asbjørn

[JohnTopley]

Make first entry in your table 0 (A2) / Takstol (B2)

[elad]

Of course... Thanks a lot - great help!!


Regards
Elad

[elad]

Hi again, one more issue has appeared....
The file will for a while have both old projects (C:\ProgramData\Hundegger\SC\bvx\31232.BVX) and new projects (C:\ProgramData\Hundegger\SC\P18-0011.37.bvx) in column C.

Is it possible to map all rows that not has P at the beginning (i.e .....bvx\31232.bvx ) to a spesific task (Ie. "Standard Task" in column E) and a spesific project (P18-0009 in column O)?

See attached file.

[elad]

Hi - anyone with a suggestion here ?


Regards
Asbjørn