| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with an embedded IF statement
I need to write an embedded IF statement for a compensation report that would
do the following: Cell A2 will be a value of 1, 2 or 3 Cell B2 will be a value of N, M or E and If cell A2 = 1 and cell B2 = N, enter text '0%' If cell A2 = 1 and cell B2 = M, enter text 'a-b%' If cell A2 = 1 and cell B2 = E, enter text 'c-d%' If cell A2 = 2 and cell B2 = N, enter text '0%' If cell A2 = 2 and cell B2 = M, enter text 'e-f%' If cell A2 = 2 and cell B2 = E, enter text 'g-h%' If cell A2 = 3 and cell B2 = N, enter text '0%' If cell A2 = 3 and cell B2 = M, enter text 'i-j%' If cell A2 = 3 and cell B2 = E, enter text 'k-l%' Your help would be much appreciated! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
Try in say, C2, array-entered with CSE*:
=IF(OR(A2="",B2=""),"",INDEX({"0%";"a-b%";"c-d%";"0%";"e-f%";"g-h%";"0%";"i-j%";"k-l%"},MATCH(1,({1;1;1;2;2;2;3;3;3}=A2)*({"N";"M";"E ";"N";"M";"E";"N";"M";"E"}=B2),0))) *press CTRL+SHIFT+ENTER to confim the formula (instead of just pressing ENTER) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lari" wrote: I need to write an embedded IF statement for a compensation report that would do the following: Cell A2 will be a value of 1, 2 or 3 Cell B2 will be a value of N, M or E and If cell A2 = 1 and cell B2 = N, enter text '0%' If cell A2 = 1 and cell B2 = M, enter text 'a-b%' If cell A2 = 1 and cell B2 = E, enter text 'c-d%' If cell A2 = 2 and cell B2 = N, enter text '0%' If cell A2 = 2 and cell B2 = M, enter text 'e-f%' If cell A2 = 2 and cell B2 = E, enter text 'g-h%' If cell A2 = 3 and cell B2 = N, enter text '0%' If cell A2 = 3 and cell B2 = M, enter text 'i-j%' If cell A2 = 3 and cell B2 = E, enter text 'k-l%' Your help would be much appreciated! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
Thank you for your reply although when I tried it it gave me errors in all
cells. Any other advise? "Max" wrote: Try in say, C2, array-entered with CSE*: =IF(OR(A2="",B2=""),"",INDEX({"0%";"a-b%";"c-d%";"0%";"e-f%";"g-h%";"0%";"i-j%";"k-l%"},MATCH(1,({1;1;1;2;2;2;3;3;3}=A2)*({"N";"M";"E ";"N";"M";"E";"N";"M";"E"}=B2),0))) *press CTRL+SHIFT+ENTER to confim the formula (instead of just pressing ENTER) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lari" wrote: I need to write an embedded IF statement for a compensation report that would do the following: Cell A2 will be a value of 1, 2 or 3 Cell B2 will be a value of N, M or E and If cell A2 = 1 and cell B2 = N, enter text '0%' If cell A2 = 1 and cell B2 = M, enter text 'a-b%' If cell A2 = 1 and cell B2 = E, enter text 'c-d%' If cell A2 = 2 and cell B2 = N, enter text '0%' If cell A2 = 2 and cell B2 = M, enter text 'e-f%' If cell A2 = 2 and cell B2 = E, enter text 'g-h%' If cell A2 = 3 and cell B2 = N, enter text '0%' If cell A2 = 3 and cell B2 = M, enter text 'i-j%' If cell A2 = 3 and cell B2 = E, enter text 'k-l%' Your help would be much appreciated! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
"Lari" wrote:
Thank you for your reply although when I tried it it gave me errors in all cells. Think you might not have confirmed the array formula properly over there .. Here's a quick sample implementation to illustrate: http://cjoint.com/?grdMGMjkht Lari_wks.xls Place the formula below into C2's formula bar, then *array-enter* the formula by pressing CTRL+SHIFT+ENTER [CSE] (instead of just pressing ENTER): =IF(OR(A2="",B2=""),"",INDEX({"0%";"a-b%";"c-d%";"0%";"e-f%";"g-h%";"0%";"i-j%";"k-l%"},MATCH(1,({1;1;1;2;2;2;3;3;3}=A2)*({"N";"M";"E ";"N";"M";"E";"N";"M";"E"}=B2),0))) If confirmed correctly, Excel will auto-insert & wrap curly braces: { } around the formula. (Do not type these curly braces into the formula itself!) Note that array-entering using CSE has to be re-done should the formula be edited subsequently. With the formula in C2 correctly array-entered, just copy C2 down to return correspondingly as required for other pairs of values in A3:B3, A4:B4, etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
Here's a quick sample implementation to illustrate:
http://cjoint.com/?grdMGMjkht Lari_wks.xls Just detected, sorry .. Pl note the above sample was inadvertently saved in manual calc mode. Before testing it out, change it back to auto calc mode via clicking: Tools Options Calculation tab Check Automatic OK (otherwise nothing will appear to happen <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
=IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3, "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3, "k-l%","")))))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552055 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
Thank you for your reply, although when I tried it on my spreadsheet it gave
me the correct '0%' for anybody who had a N value in B2 and a blank field for all others. Any other advise? "Bearacade" wrote: =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3, "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3, "k-l%","")))))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552055 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
The formula are checking A2 and B2, is this formula in C2? and when you say for all others? where are they in the range? A3, B3, C3? Lari Wrote: Thank you for your reply, although when I tried it on my spreadsheet it gave me the correct '0%' for anybody who had a N value in B2 and a blank field for all others. Any other advise? "Bearacade" wrote: =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3, "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3, "k-l%","")))))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552055 -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552055 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with an embedded IF statement
Yes, the formula is in C2. And 'all others' are in cells A3, B3 and C3...and
on down the spreadsheet of 500 rows. "Bearacade" wrote: The formula are checking A2 and B2, is this formula in C2? and when you say for all others? where are they in the range? A3, B3, C3? Lari Wrote: Thank you for your reply, although when I tried it on my spreadsheet it gave me the correct '0%' for anybody who had a N value in B2 and a blank field for all others. Any other advise? "Bearacade" wrote: =IF(B2="N", "0%", IF(B2="M", IF(A2=1, "a-b%", IF(A2=2, "e-f%", IF(A2=3, "i-j%",""))), IF(B2="E", IF(A2=1, "c-d%", IF(A2=2, "g-h%", IF(A2=3, "k-l%","")))))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552055 -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552055 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| list embedded in an if statement | Excel Discussion (Misc queries) | |||
| reducing the number of times an IF statement needs to be calculated in embedded IF statements | Excel Discussion (Misc queries) | |||
| If statement | Excel Discussion (Misc queries) | |||
| Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
| Displaying cell references next to embedded cells in Word 2000 | New Users to Excel | |||
Hybrid Mode
