Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If+if+if+if... Does anyone know a better way?
Hi. I have this equation:
=(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0)) +(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0)) +if+if+if... and so on... Which means: if (date = startdate and date <= endate) then show the value for that day, or 0 if date doesn't fall inbetween the start and end date... Which is working but I need to do this for every day of the month so: a/ Excel won't let me write an equation long enough to have 31 of these and b/ it's a real pain having to write it out for every month! Does anyone know a better way?! Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If+if+if+if... Does anyone know a better way?
Hi
Since your talking about 31 days, then I guess the start date is the beginning of a month and the end date is the last date of the month. If so, perhaps you could use something like =IF(MONTH(Q10=MONTH(C10),J10,0) or, if you have the Analysis Toolpack loaded (ToolsAddinsAnalysis Toolpack) then may =IF(Q10<=EOMONTH(C1,0),J10,0) Insert your absolute references to suit. -- Regards Roger Govier "Statler" wrote in message ... Hi. I have this equation: =(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0)) +(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0)) +if+if+if... and so on... Which means: if (date = startdate and date <= endate) then show the value for that day, or 0 if date doesn't fall inbetween the start and end date... Which is working but I need to do this for every day of the month so: a/ Excel won't let me write an equation long enough to have 31 of these and b/ it's a real pain having to write it out for every month! Does anyone know a better way?! Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If+if+if+if... Does anyone know a better way?
Use
=SUMPRODUCT(--(C10:C24<=Q10),--(D10:D24=Q10),J10:J24) change the 24 to your last cell -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Statler" wrote in message ... Hi. I have this equation: =(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0)) +(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0)) +if+if+if... and so on... Which means: if (date = startdate and date <= endate) then show the value for that day, or 0 if date doesn't fall inbetween the start and end date... Which is working but I need to do this for every day of the month so: a/ Excel won't let me write an equation long enough to have 31 of these and b/ it's a real pain having to write it out for every month! Does anyone know a better way?! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|