Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
Awhile back, I came here and found a formula for an IF statement that had
more than 7 IFs. I put it into my spreadsheet and it worked perfectly...well I saved over it and now it's gone :( I hope someone here can get me the correct formula again. Here is my problem: cell AR3 contains the month (1 for July, 2 for August, etc) and changes each month. Cells BR4:BR19 contain year-to-date totals for our 16 water systems from the prior year. The IF statement was something like =IF(AR$3<=1, AT4), IF(AR$3<=2, AT4+AV4), IF(AR$3<=3, AT4+AV4+AX4), etc... Obviously, this isn't correct because it doesn't work...LOL! I need column BR to only add January when AR3=1, add January + February when AR3=2, add January + February + March when AR3=3, etc. Clear as mud, right? If anyone can help, I will be so very grateful!!! THANKS!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
this is the original formula with the 7 if's...... =IF(A14='Foreman''s DTC(7)'!$D$12,'Foreman''s DTC(7)'!$E$39,IF(A14='Foreman''s DTC(7)'!$F$12,'Foreman''s DTC(7)'!$G$39,IF(A14='Foreman''s DTC(7)'!$H$12,'Foreman''s DTC(7)'!$I$39,IF(A14='Foreman''s DTC(7)'!$J$12,'Foreman''s DTC(7)'!$K$39,IF(A14='Foreman''s DTC(7)'!$L$12,'Foreman''s DTC(7)'!$M$39,IF(A14='Foreman''s DTC(7)'!$N$12,'Foreman''s DTC(7)'!$O$39,IF(A14='Foreman''s DTC(7)'!$P$12,'Foreman''s DTC(7)'!$Q$39,0))))))) with the help of somebody in this discussion board....this was the end result.... =IF(ISNA(MATCH($A10,'DTC(1)'!$12:$12,0)),"",INDEX( 'DTC(1)'!$37:$37,MATCH($A10,'DTC(1)'!$12:$12,0))) hope this helps... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
Try one of these array formulas** :
If you will *never* insert new columns before the range: =SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1)),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1))) If you might insert new columns before the range: =SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1))-COLUMN(AT4),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Blessedx3" wrote in message ... Awhile back, I came here and found a formula for an IF statement that had more than 7 IFs. I put it into my spreadsheet and it worked perfectly...well I saved over it and now it's gone :( I hope someone here can get me the correct formula again. Here is my problem: cell AR3 contains the month (1 for July, 2 for August, etc) and changes each month. Cells BR4:BR19 contain year-to-date totals for our 16 water systems from the prior year. The IF statement was something like =IF(AR$3<=1, AT4), IF(AR$3<=2, AT4+AV4), IF(AR$3<=3, AT4+AV4+AX4), etc... Obviously, this isn't correct because it doesn't work...LOL! I need column BR to only add January when AR3=1, add January + February when AR3=2, add January + February + March when AR3=3, etc. Clear as mud, right? If anyone can help, I will be so very grateful!!! THANKS!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
Sandy,
The first IF statement works perfectly. Thank you! "Sandy Mann" wrote: Perhaps it was something like: =IF(AR$3=1,AT4,0)+IF(AR$3=2,AT4+AV4,0)+IF(AR$3=3,A T4+AV4+AX4,0)+...........) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
Thanks for your reply.
"jcheko" wrote: this is the original formula with the 7 if's...... =IF(A14='Foreman''s DTC(7)'!$D$12,'Foreman''s DTC(7)'!$E$39,IF(A14='Foreman''s DTC(7)'!$F$12,'Foreman''s DTC(7)'!$G$39,IF(A14='Foreman''s DTC(7)'!$H$12,'Foreman''s DTC(7)'!$I$39,IF(A14='Foreman''s DTC(7)'!$J$12,'Foreman''s DTC(7)'!$K$39,IF(A14='Foreman''s DTC(7)'!$L$12,'Foreman''s DTC(7)'!$M$39,IF(A14='Foreman''s DTC(7)'!$N$12,'Foreman''s DTC(7)'!$O$39,IF(A14='Foreman''s DTC(7)'!$P$12,'Foreman''s DTC(7)'!$Q$39,0))))))) with the help of somebody in this discussion board....this was the end result.... =IF(ISNA(MATCH($A10,'DTC(1)'!$12:$12,0)),"",INDEX( 'DTC(1)'!$37:$37,MATCH($A10,'DTC(1)'!$12:$12,0))) hope this helps... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
Thanks for your reply.
"T. Valko" wrote: Try one of these array formulas** : If you will *never* insert new columns before the range: =SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1)),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1))) If you might insert new columns before the range: =SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1))-COLUMN(AT4),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Blessedx3" wrote in message ... Awhile back, I came here and found a formula for an IF statement that had more than 7 IFs. I put it into my spreadsheet and it worked perfectly...well I saved over it and now it's gone :( I hope someone here can get me the correct formula again. Here is my problem: cell AR3 contains the month (1 for July, 2 for August, etc) and changes each month. Cells BR4:BR19 contain year-to-date totals for our 16 water systems from the prior year. The IF statement was something like =IF(AR$3<=1, AT4), IF(AR$3<=2, AT4+AV4), IF(AR$3<=3, AT4+AV4+AX4), etc... Obviously, this isn't correct because it doesn't work...LOL! I need column BR to only add January when AR3=1, add January + February when AR3=2, add January + February + March when AR3=3, etc. Clear as mud, right? If anyone can help, I will be so very grateful!!! THANKS!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with if statement (more than 7)
You're welcome!
-- Biff Microsoft Excel MVP "Blessedx3" wrote in message ... Thanks for your reply. "T. Valko" wrote: Try one of these array formulas** : If you will *never* insert new columns before the range: =SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1)),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1))) If you might insert new columns before the range: =SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1))-COLUMN(AT4),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Blessedx3" wrote in message ... Awhile back, I came here and found a formula for an IF statement that had more than 7 IFs. I put it into my spreadsheet and it worked perfectly...well I saved over it and now it's gone :( I hope someone here can get me the correct formula again. Here is my problem: cell AR3 contains the month (1 for July, 2 for August, etc) and changes each month. Cells BR4:BR19 contain year-to-date totals for our 16 water systems from the prior year. The IF statement was something like =IF(AR$3<=1, AT4), IF(AR$3<=2, AT4+AV4), IF(AR$3<=3, AT4+AV4+AX4), etc... Obviously, this isn't correct because it doesn't work...LOL! I need column BR to only add January when AR3=1, add January + February when AR3=2, add January + February + March when AR3=3, etc. Clear as mud, right? If anyone can help, I will be so very grateful!!! THANKS!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
IF statement(?) | Excel Discussion (Misc queries) | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |