| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying -1,0 or 1 depending on cell value?
Hi all, Im trying to get a cell to display either 1, 0 or -1 depending on other cells product here's what i'm using =IF(E5=0,0,IF(OR(B$5-E5<0,B$5-E54),0,VLOOKUP(B$5-E5,{-1,1;-2,1;-3,1;1,-1;2,-1;3,-1},2))) i can get itto display 0 or -1 or 0 and 1, my criteria is this if E5 B5 by any amount then display -1 if E5 = B5 then display 0 and if E5<B5 by any amount display 1. I cant seem to manage it with the formula above, any ideas? Regards, Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=546345 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Displaying -1,0 or 1 depending on cell value?
=sign(B5-E5)
-- Regards, Tom Ogilvy "Simon Lloyd" wrote in message ... Hi all, Im trying to get a cell to display either 1, 0 or -1 depending on other cells product here's what i'm using =IF(E5=0,0,IF(OR(B$5-E5<0,B$5-E54),0,VLOOKUP(B$5-E5,{-1,1;-2,1;-3,1;1,-1;2, -1;3,-1},2))) i can get itto display 0 or -1 or 0 and 1, my criteria is this if E5 B5 by any amount then display -1 if E5 = B5 then display 0 and if E5<B5 by any amount display 1. I cant seem to manage it with the formula above, any ideas? Regards, Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=546345 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Displaying -1,0 or 1 depending on cell value?
One way:
=SIGN(B5-E5) In article , Simon Lloyd wrote: my criteria is this if E5 B5 by any amount then display -1 if E5 = B5 then display 0 and if E5<B5 by any amount display 1. I cant seem to manage it with the formula above, any ideas? Regards, Simon |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Displaying -1,0 or 1 depending on cell value?
JE & Tom, Thanks!!!! i can't believe it was that simple, i was looking at a very long winded complicated way.....you know...sledge hammer to crack a nut! Cheers! Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=546345 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Displaying a number with different decimals depending on a condition | Excel Worksheet Functions | |||
| How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
| How do I change the color of a cell depending on the result of the function in that cell | Excel Programming | |||
| Automated cell copy depending on cell content? | Excel Programming | |||
| displaying varying toolbars depending on workbook | Excel Programming | |||
![Simon Lloyd[_740_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
