Help With Excel Formulas

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Help With Excel Formulas

Post by hlewton »

I have a spreadsheet that someone a while ago did the formulas for me. The formulas still work great, but I would like to make the shell document look better by having the cells these formulas are in appear blank if there is no data entries in the spread sheet yet. All the data entries are numbers no text. I cannot figure out how to make that happen. The formula appears first and what its results display without any data entries is listed after the word result and in bold. Can someone please tell me what modifications I need to make to these formulas to get the cells to display blank until data is entered.

The first formula I copied is actually =AVERAGE(B3:B39) but what you see below was my attempt to get a blank cell but it failed.


=(IF(AVERAGE(B3:B39)<>0,(AVERAGE(B3:B39)),"")) result #DIV/0!


=CONCATENATE(MAX(B3:B39)," & ",VLOOKUP(MAX(B3:B39),B3:C39,2,FALSE)) result #N/A


=CONCATENATE(MAX(C3:C39)," & ",VLOOKUP(MAX(C3:C39),CHOOSE({1,2},C3:C39,B3:B39),2,FALSE)) result 0 & 0
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

Use:

=IFERROR(AVERAGE(B3:B39),"")

=IFERROR(CONCATENATE(MAX(B3:B39)," & ",VLOOKUP(MAX(B3:B39),B3:C39,2,FALSE)),"")

I don't know what the third formula is meant to do.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
17 Dec 2020, 19:27
Use:

=IFERROR(AVERAGE(B3:B39),"")

=IFERROR(CONCATENATE(MAX(B3:B39)," & ",VLOOKUP(MAX(B3:B39),B3:C39,2,FALSE)),"")

I don't know what the third formula is meant to do.
First thanks for the 2 formulas so far. I will try to explain what the third formula is used for. Let me start out by defining what the second formula is doing. The second formula looks for the maximum entry in column B and chooses the corresponding entry in column C. The third formula looks for the maximum entry in column C and chooses the corresponding entry in column B. Actually these data points I have to enter are blood pressure readings (systolic and diastolic pressures) so I can relay them to my hypertension specialist. So the second formula looks for the maximum systolic and the 3rd for the maximum diastolic and there corresponding pressures.

I hope that helps explain what I am trying to do. I have to take your examples and then try to make them work for the same type of formulas as the 2nd and 3rd only using Minimum instead of maximum.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

Try this:

=IF(COUNT(C3:C39)=0,"",CONCATENATE(MAX(C3:C39)," & ",VLOOKUP(MAX(C3:C39),CHOOSE({1,2},C3:C39,B3:B39),2,FALSE)))
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thank you.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
17 Dec 2020, 19:27
Use:

=IFERROR(AVERAGE(B3:B39),"")

Hans everything is working great thanks to you but I would like to ask a question for my education. I had other formulas in the spreadsheet and thought if I followed your examples I could figure them out. Well I did get them working. I had a need to use the =MAX and =MIN functions. I got them to work using:

=(IF(MAX(B3:B39)<>0,(MAX(B3:B39)),""))

=(IF(MIN(B3:B39)<>0,(MIN(B3:B39)),""))


These gave me the blank cells I wanted.

I tried using the =IFERROR, as you did in the =AVERAGE function, and that still gave a 0, not a blank cell as I wanted.

I'm not sure the functions I listed above that I came up with are the correct ones or best ones to use but they seem to work. Why didn't the =IFERROR work?

By the way, before I asked for your help I used the same function syntax for the =AVERAGE function, "=(If(Max(B3:B39)<>0,(Max(B3:B39)),""))" and it too did not give me a blank cell.

Just curious when I should use the =IFERROR or the <>0 type of syntax.

Sure hope I phrased this so you can understand what I'm asking.

Thanks
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

MIN and MAX always return a number, at least if the range they refer to does not contain errors.
If the range doesn't contain any numbers, MIN and MAX will return 0.
The same holds for SUM.

But AVERAGE divides the sum of the cells by the count of numeric values: AVERAGE(range) is equivalent to SUM(range)/COUNT(range).
If there are no numeric values, AVERAGE divides by zero, so it returns the error value #DIV/0!
We can trap this using IFERROR.

Since MIN and MAX don't return an error if there are no numeric values, but 0 instead, you have to check for 0 instead of using IFERROR.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thank you for that explanation.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

You’d think I could get this figured out but I have spent well over an two and a half hours trying and only get error dialog boxes that I don’t understand. I am trying to use this formula =COUNTIF(B4:B49,">=150 ") to count the times a value is greater than or equal to 150 in the defined range of B4:B49. That alone seems to work but then I have tried to make the cell the formula is in to be blank if there are no values of 150 or greater. How do I modify the formula to show blank if there are no values 150 or greater?

Thanks
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

One option would be to hide a zero return in the cell by setting the number format to the custom format 0;;

Another option is to change the formula to

=IF(COUNTIF(B4:B49,">=150")=0,"",COUNTIF(B4:B49,">=150"))
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
03 Jan 2021, 16:33
One option would be to hide a zero return in the cell by setting the number format to the custom format 0;;

Another option is to change the formula to

=IF(COUNTIF(B4:B49,">=150")=0,"",COUNTIF(B4:B49,">=150"))
Thank you very much.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Hans I used the formula =IF(COUNTIF(B4:B49,">=150")=0,"",COUNTIF(B4:B49,">=150")) and it works perfectly. I tried to take it one step further. The formula is in cell B50 and I wanted to enter a formula to figure percentage of the value in B50/28 in cell C50. I wanted it to again be blank if there was no value in B50 to divide by 28. Again I have tried variations of this =IF(B50>0,B50/28,"") formula and it doesn't give me a blank cell. I will probably never get this in my head so once again, will you please tell me the formula to use to place in Cell C50 and only show a value if there is a value in cell B50? Thanks.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

You have to check for B50 being equal to an empty string "":

=IF(B50="",""B50/28)

Checking B50>0 won't work, since Excel considers any string, even the empty string "", as greater than any number.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thanks again. I hope I am done now.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Well, I tried and believe I got the function CONCATENATE to work on a formula but there is a problem. =IF(B50="","",B50/(COUNTA(B4:B37))) is the formula used with the cell formatted to a percentage with 0 decimal places and the result is 21%. After changing the formula to add the text “Number of Readings” using this formula =IF(B50="","",CONCATENATE(B50/(COUNTA(B4:B37)))&" Number Of Readings") the 21 changes to 0.214285714285714 Number Of Readings without the % sign showing.
How do I use CONCATENATE to add the words and keep the 21% showing. In other words I would like the end results read 21% Number Of Readings.
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

Do you want the cell to be empty if there are no readings? If so:

=IF(B50="","",TEXT(B50/COUNTA(B4:B37),"0%")&" Number Of Readings")

CONCATENATE was superfluous, it didn't do anything in your formula.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
04 Jan 2021, 14:57
Do you want the cell to be empty if there are no readings? If so:

=IF(B50="","",TEXT(B50/COUNTA(B4:B37),"0%")&" Number Of Readings")

CONCATENATE was superfluous, it didn't do anything in your formula.
Yes I want the cell to be blank to start with. Thank you for the new formula.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

I worked out the formula below and it does most of what I want it to do. You can see that it counts the number of cells in range C68:C437 that contains the word “OK.”

=IF(COUNTIF($C$68:$C$437,"OK")="","",COUNTIF($C$68:$C$437,"OK"))&" Days in a row OK"

The above formula in this portion of the spreadsheet yields “220 Days in a row OK” in whatever cell it is pasted into. However, I cannot seem to get it to yield a blank cell if there are no cells with the word “OK” in them. I thought the portion I have in bold (="","",) would do that but it doesn’t. What change or changes do I need to this formula to get a blank cell if there is no cells containing “OK”?
Regards,
hlewton

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

If there are no cells with OK, COUNTIF returns 0, not "". And you should place &" Days in a row OK" inside the parentheses of the IF function.

=IF(COUNTIF($C$68:$C$437,"OK")=0,"",COUNTIF($C$68:$C$437,"OK")&" Days in a row OK")
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2817
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
10 Jan 2021, 20:26
If there are no cells with OK, COUNTIF returns 0, not "". And you should place &" Days in a row OK" inside the parentheses of the IF function.

=IF(COUNTIF($C$68:$C$437,"OK")=0,"",COUNTIF($C$68:$C$437,"OK")&" Days in a row OK")
Thank you Hans. I got to say I thought I had tried that and many more others things but I kept getting an error dialog box that when it closed it highlighted where it wanted me to make a change but I kept trying wrong things, I guess.
Regards,
hlewton