Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 10-10-2023, 10:45 PM
ArviLaanemets ArviLaanemets is offline Need cell to return NULL if there is a formula in referenced cell Windows 8 Need cell to return NULL if there is a formula in referenced cell Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

An prefect example why I never use row below the last one of the table for any information - be it formula, or some manual entry!

Instead I put such info at top of sheet - above table headers. An additional bonus is, that when I apply Freeze Panes feature to have column headers always be shown, this info at top of page will always visible too.

As I have all my tables Defined Tables, it will be easy to calculate any counts or totals or whatever for any column in row(s) reserved for them at top of page. In case you use regular Excel tables, you either define your dataranges of your table columns as dynamic ranges, or you have to refer in totals formula for range reahcing down enough to work for some amount of time (e.g. your table has for column C values in range $C$5:$C$9, but to get the sum of this table column you calculate it for range $C$5:$C$1000), and you keep the cells below you table free of any additional info.

About your current problem, in case the formula returns a non-numeric value, you can check for this easily. Like
= IF(ISNUMBER($T2),MAX(0,$T2-TODAY(),"")
NB! You see that you don't need to check the cell being empty separately, as the value of empty cell's is not numeric!

Also, in case the cell below table contains e.g. count (which is reasonable, as I can't see how you want to sum all dates), you can check for the number being too small to be a reasonable date. E.g. when you are sure in your table will never be any dates from previous century, this will probably work for you
= IF(1*$T2>36525,MAX(0,$T2-TODAY(),"")
NB! Again you don't need to check the cell being empty separately, as the value of empty cell multiplied by any number is 0, i.e. is not greater than 36525!

And as last - why have you copied your formula down to this wrong row at all? From your example formula, your calculation is based on current row of formula! An afterthought - when it is because you use a Defined Table, and the formula is expanding automatically and counting the last row having any data as part of Table, then read the top rows of my answer!
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
I want my formula to result in zero if referenced cell = 0, otherwise calculate Daronovitz Excel 2 12-21-2022 11:44 AM
Filling a formula down a column where only one cell value referenced changes? sakurasanta86 Excel 1 08-27-2018 03:23 AM
Need cell to return NULL if there is a formula in referenced cell Filling a formula down a column where only one cell value referenced changes? takamaz Excel 2 03-11-2018 08:56 AM
Random cell return using vlookup formula mattbeaves Excel Programming 3 05-22-2017 04:38 AM
Need cell to return NULL if there is a formula in referenced cell Formula to return next non-blank cell in a range Anyroad Excel 4 09-15-2015 04:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft