人気ブログランキング | 話題のタグを見る

calculating on an expanding rows and columns

calculating on an expanding rows and columns

I've got some really great formulas that reference two cells which are populated with a start and end date. 
The formulas (ifs, countifs, if/countif combined, stdev, average, some arrays etc) are on one spreadsheet and the data is on another. 
If there a way to write a formula that leaves the end of the data unspecified?
Each formula and case is unique.  Here is an example where on the row is effected because this formula would be copied and pasted.  The bolded portions are the numbers that should be open ended.  
=IF(ISNUMBER(SEARCH("c*",AttendanceT4!C$2)),"C", (IF(COUNTIFS(AttendanceT4!$B$2:$B$95,"CM",AttendanceT4!C$2:C$95,"x")>0,1,0)))
I have other formulas that current analyze data populated in B2:AZ95 that in 6 months might be in B2:BZ100.
I could arbitrarily name a range but that doesn't seem like a fabulous solution.
Thoughts?

Solutions to the Problem calculating on an expanding rows and columns

Download SmartPCFixer for Free Now

yes, BH10 & BH11 are merged (BI10&11, BJ10&11 etc), but strictly for display purposes. 
I can change it if it makes it easier.

Ok, try something like this...
Dynamic ranges:
Dates, refers to =$BH$10:INDEX($BH$10:$XFD$10,COUNT($BH$10:$XFD$10)) Values, refers to =$BH$12:INDEX($BH$12:$XFD$12,COUNT($BH$10:$XFD$10))
Then, your normally entered formula becomes (no need for an array formula here):
=AVERAGEIFS(Values,Dates,">="&$O$1,Dates,"<="&$O$2)
--
Biff
Microsoft Excel MVP

In this method we will try to Boot in Safe mode with Networking and see if the error still persists.

  • Search "MSCONFIG" and Open.
  • Click on Boot Tab
  • Tick Safe Boot in Boot Option
  • Select Network.
  • Click Apply and OK.
  • Reboot your System.

Another Safe way to Repair the Problem: calculating on an expanding rows and columns:

How to Fix calculating on an expanding rows and columns with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer.  Open it, and it will scan your computer. The junk files will be shown in the scan result.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in this software. Like dll downloading, windows updating and print spooler error repair.


Related: How to Update ASUS Xonar D2X driver for Windows XP 32/64-bit,Update ATI VGA Driver version 6.13.10.5779,How to Download Chicony Camera Driver version 61.005.029.1900,Download OKI C9800(PCL),How to Update TI Card Reader Driver version 2.0.0.10,Method to Download Toshiba Portege R835-P83 eco Utility v.1.3.21.0 driver,Method to Download Toshiba Satellite A660-BT2N23 Face Recognition v.3.1.3 driver,Download Toshiba Satellite L355-S7907 Face Recognition v.2.0.17.32 driver,[Solved] Download Toshiba Satellite L740-BT4N11 PC Health Monitor Utility v.1.7.9.64 driver,How to Download Toshiba Satellite P305D-S8828 WPS Library v.1.0.0.7 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17
Read More: Troubleshooting:Calculating\/recording the last date\/time a cell was changed,How to Fix Error - Can I record CD\'s using my DVD recorder that will play on ordinary CD players??,Troubleshoot:Can I make a vlookup formula look in more than 1 table? Error,Troubleshoot:Can Flight Simulator 98 run on Windows 7? Error,Solution to Error: cambridge advance learner dictionary is not running on windows 7 os.,BSOD after upgraded to Windows 8.1 (memory problem?),Blurry programs on Windows 8.1,C:\Window|Microsoft.Net\Framework\v4.0.30319\mscoreei.dll could not be loaded,Browsing history is no longer visible,Bulk deleting from email deleted items folder
by rpcserverisuna | 2016-08-26 15:18