Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten an IF formula
Is there anyway to shorten an IF formula? What I am trying to do is display a
certain number that coinsides with the date. Here is the formula that I am currently using. It works but it is too long and the "Formula Is Too Long" error pops up when I have too many days. =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sh eet2!E2,0)+IF(C11=Sheet2!C3,Sheet2!E3,0). In the first part of the formula C11 is today's date. Sheet2!C1 is any date. Sheet2!E1 is a number. I need a the cell to display the number if today's date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank you for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten an IF formula
=VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)
Or, if you don't like the #NA if there is no matching date, =IF(ISNA(VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)),"",VL OOKUP(C11,Sheet2!C1:E91,3,FALSE)) -- Kind regards, Niek Otten "Joker" wrote in message ... Is there anyway to shorten an IF formula? What I am trying to do is display a certain number that coinsides with the date. Here is the formula that I am currently using. It works but it is too long and the "Formula Is Too Long" error pops up when I have too many days. =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sh eet2!E2,0)+IF(C11=Sheet2!C3,Sheet2!E3,0). In the first part of the formula C11 is today's date. Sheet2!C1 is any date. Sheet2!E1 is a number. I need a the cell to display the number if today's date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank you for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten an IF formula
I cannot understand why you get an error with that, but this is shorter
=(C11=Sheet2!C1)*Sheet2!E1+(C11=Sheet2!C2)*Sheet2! E2+(C11=Sheet2!C3)*Sheet2! E3 and if C1:C3 are uniique values, this is even shorter =INDEX(Sheet2!E1:E3,MATCH(C11,Sheet2!C1:C3,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Joker" wrote in message ... Is there anyway to shorten an IF formula? What I am trying to do is display a certain number that coinsides with the date. Here is the formula that I am currently using. It works but it is too long and the "Formula Is Too Long" error pops up when I have too many days. =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sh eet2!E2,0)+IF(C11=Sheet2!C 3,Sheet2!E3,0). In the first part of the formula C11 is today's date. Sheet2!C1 is any date. Sheet2!E1 is a number. I need a the cell to display the number if today's date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank you for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten an IF formula
Thank you so much Niek. That worked perfectly. I really appreciate the help
you guys provide. "Niek Otten" wrote: =VLOOKUP(C11,Sheet2!C1:E91,3,FALSE) Or, if you don't like the #NA if there is no matching date, =IF(ISNA(VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)),"",VL OOKUP(C11,Sheet2!C1:E91,3,FALSE)) -- Kind regards, Niek Otten "Joker" wrote in message ... Is there anyway to shorten an IF formula? What I am trying to do is display a certain number that coinsides with the date. Here is the formula that I am currently using. It works but it is too long and the "Formula Is Too Long" error pops up when I have too many days. =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sh eet2!E2,0)+IF(C11=Sheet2!C3,Sheet2!E3,0). In the first part of the formula C11 is today's date. Sheet2!C1 is any date. Sheet2!E1 is a number. I need a the cell to display the number if today's date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank you for your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten an IF formula
Thanks for the reply Bob. The reason I was getting the error is because I was
using each day as another IF formula. I had 91 days so I had 91 formulas added together. I only included the first three in my post. Excel apparently only lets you have 1240 or so characters. That is the reason for the error. I really appreciate the quick reply. Thank you. "Bob Phillips" wrote: I cannot understand why you get an error with that, but this is shorter =(C11=Sheet2!C1)*Sheet2!E1+(C11=Sheet2!C2)*Sheet2! E2+(C11=Sheet2!C3)*Sheet2! E3 and if C1:C3 are uniique values, this is even shorter =INDEX(Sheet2!E1:E3,MATCH(C11,Sheet2!C1:C3,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Joker" wrote in message ... Is there anyway to shorten an IF formula? What I am trying to do is display a certain number that coinsides with the date. Here is the formula that I am currently using. It works but it is too long and the "Formula Is Too Long" error pops up when I have too many days. =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sh eet2!E2,0)+IF(C11=Sheet2!C 3,Sheet2!E3,0). In the first part of the formula C11 is today's date. Sheet2!C1 is any date. Sheet2!E1 is a number. I need a the cell to display the number if today's date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
self-updating formula | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Shorten sumproduct formula | Excel Discussion (Misc queries) |