This is so simple, that even I find it easy to do, with a formula...
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
|
|
2 |
NSE |
ACC |
EQ |
1087 |
1030 |
955.5 |
998.45 |
1079.9 |
22 |
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H")) |
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H")) |
3 |
NSE |
ADANIPOWER |
EQ |
148.05 |
27.75 |
25.65 |
25.65 |
146.5 |
25 |
=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H")) |
=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H")) |
4 |
NSE |
DLF |
EQ |
265 |
419.7 |
350.05 |
387.25 |
267.15 |
17388 |
=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H")) |
=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H")) |
5 |
NSE |
AMBUJACEM |
EQ |
30.4 |
155.8 |
142.55 |
145.85 |
29.95 |
15083 |
=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H")) |
=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H")) |
Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
|
|
2 |
NSE |
ACC |
EQ |
1087 |
1030 |
955.5 |
998.45 |
1079.9 |
22 |
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H")) |
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H")) |
3 |
NSE |
ADANIPOWER |
EQ |
148.05 |
27.75 |
25.65 |
25.65 |
146.5 |
25 |
=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H")) |
=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H")) |
4 |
NSE |
DLF |
EQ |
265 |
419.7 |
350.05 |
387.25 |
267.15 |
17388 |
=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H")) |
=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H")) |
5 |
NSE |
AMBUJACEM |
EQ |
30.4 |
155.8 |
142.55 |
145.85 |
29.95 |
15083 |
=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H")) |
=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H")) |
Worksheet: 1-Sheet1
Each formula uses 2 Excel If functions, one is nested in the other...
Code:
( If _>_ , Do this , Else [ If _<_ , Do this , Else "......" ] )
If ( _>_ , Do this , Else If( _<_ , Do this , Else "......" ) )
Column J
Code:
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))
if column H is greater than column D then calculate 1% of column H
=IF(H2>D2,1/100*H2,
Else
if column H is lower than column D then calculate 1% of column H
IF(H2<D2,1/100*H2,
Else
"D is equal to H"))
Column K
Code:
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))
if column H is greater than column D subtract Column H & column J
=IF(H2>D2,H2-J2,
Else
if column H is lower than column D add Column H & column J
IF(H2<D2,H2+J2,
Else
"D is equal to H"))
Alan
Ref
https://support.office.com/en-us/art...rs=en-US&ad=US
https://www.techonthenet.com/excel/formulas/if.php
Bookmarks