Help With Excel Formulas

[hlewton]

I just went back and looked at all the iterations of that formulas I had. I had all that is listed above but did not have the last parenthesis after " Days in a row OK" I had it after the second OK in front of the &. In the cell where the formula was, when there were no "OK" it displayed " Days in a row OK" without the quotes. Thanks again

[hlewton]

Using this formula, =IF($F$24/$F$26="","",TEXT( ($F24/$F$26),"0%")&" Right Failures"), I get the cell to display what I want to see in it, like 12 Right Failures. As you can see the formula is based on numbers in cells F24 and F26.

I then entered this formula in cell F24, =IF(COUNTIF($C$3:$C$400,"Right")=0,"",COUNTIF($C$3:$C$400,"Right")&" Times Right Failed"), It displays, 12 Times Right Failed.


I entered this one in cell F26, =IF(COUNTA($C$3:$C$400)=0,"",COUNTA($C3:C400)&" Total failures"), It displays 13 Total Failures.

Without the added text they produce the way I would like to see the cells displayed. However, the cells F24 and F26 now display 12 Times Right failed and 13 Total failures. In my first formula above I would like to be able to use the 12 and 13, ignoring the text, from the other 2 above formulas to produce this, 92% Right Failures, in the cell where I enter the first formula? Is this possible and how would I edit or replace the formula I used?

[HansV]

You'd have to use something like this:

=IF(OR(COUNTIF($C$3:$C$400,"Right")=0,COUNTA($C$3:$C$400)=0),"",TEXT(COUNTIF($C$3:$C$400,"Right")/COUNTA($C$3:$C$400),"0%")&" Right Failures")

[hlewton]

Thank you. I will try that.

[hlewton]

=(IF(MAX($B$3:$B$402)<>0,(MAX($B$3:$B402)),""))

=IFERROR(AVERAGE($B$3:$B$402),"")

The above formulas do work as intended since you fixed them for me. But I am at it again and still can’t come up with the correct modification to those formulas so I can get the results I want. The cell that contains the formula for maximum, If 57, for example, is the maximum I’d like it to show “57 is the maximum” without the quotes.

The cell for average I’d like it to show “57 is the average” Will you please fix them again for me? Thanks.

[HansV]

1) For the maximum:

=IF(MAX($B$3:$B$402)<>0,MAX($B$3:$B402)&" is the maximum","")

(I removed some superfluous parentheses)

2) For the average:

=IFERROR(AVERAGE($B$3:$B$402)&" is the average","")

Please keep in mind that the formulas now return a text string instead of a number, so you cannot use the cells in calculations.

[hlewton]

Thank you again. Yep I found that out about the calculations. I even tried searching for ways to extract the value from similar cells and gave up on that too.

[HansV]

Alternatively, use the following for the maximum:

=MAX($B$3:$B$402)

and apply the custom number format

General" is the maximum";;

to the cell with the formula. The actual value of the cell will now be a number, but it will be displayed with extra text.

For the average, use the original formula

=IFERROR(AVERAGE($B$3:$B$402),"")

and apply the custom number format

General" is the average"

[hlewton]

Thanks I just tried that and, as you know, it worked. These are the simpler formulas of all that you have helped me with. Not sure how the others would work with all the If, IFERROR, COUNTIF, and COUNTA functions in them. But I don't need then now but curious if that is even possible to do with such functions.

[HansV]

Instead of

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

you could use

=IF(B50="","",B50/COUNTA(B4:B37))

with the custom number format

0%" Number Of Readings"

Instead of

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

you could use

=COUNTIF($C$68:$C$437,"OK")

with the custom number format

0" Days in a row OK";;

Instead of

=IF(OR(COUNTIF($C$3:$C$400,"Right")=0,COUNTA($C$3:$C$400)=0),"",TEXT(COUNTIF($C$3:$C$400,"Right")/COUNTA($C$3:$C$400),"0%")&" Right Failures")

you could use

=IFERROR(COUNTIF($C$3:$C$400,"Right")/COUNTA($C$3:$C$400),"")

with custom number format

0%" Right Failures";;

[hlewton]

WOW that is great to know. Thank you.

[hlewton]

Have another question.
In the attached spreadsheet I wanted the cell in "column I" to be blank if there was no data in the cell in "column B." The formula you see in "column I" outlined in red does what I want but I am confused. I thought "column B’s" cell would have to be “greater than” null for what I wanted to work but it only works if it is “less than” null as seen in the formula. If I use the “greater than” sign it makes the cells in "column I" show as blank with or without data being in the corresponding cell in "column B." Where has my thinking gone wrong it trying to create this formula?

Test.jpg

[HansV]

Excel treats all text values, including the empty string "", as greater than all numeric and date values.
I would write the formula as

=IF(B6="","",B6-A6+1)

That looks clearer to me.

[hlewton]

Excel treats all text values, including the empty string "", as greater than all numeric and date values.
I would write the formula as

=IF(B6="","",B6-A6+1)

That looks clearer to me.

OK, thank you and I will do that.

[hlewton]

Hans I need a little help again with 2 of the formulas you made for me. They work great in the spreadsheet they were designed for. I modified them by only changing the ranges of cells in order for them to work in another spreadsheet. They work but I cannot format the results to only display full numbers with nothing to the right of the decimal point. I tried copying the format form the sheet where they display the way described but that didn't work. To be honest, I'm not even sure how they are formatted in that sheet. Here are the formulas. As you can see below, the maximum value does display correctly when figuring the max value but the minimum value displays with numbers after the decimal point. Both values display with numbers after the decimal point when figuring the minimum value.

They do display the correct numbers but I can't get them to just display full numbers.

Max

=IFERROR(CONCATENATE(MAX(J377:J480)," & ",VLOOKUP(MAX(J377:J480),J377:K480,2,FALSE)),"")

148 & 74.3571428571429




=IF(COUNT(K377:K480)=0,"",CONCATENATE(MIN(K377:K480)," & ",VLOOKUP(MIN(K377:K480),CHOOSE({1,2},K377:K480,J377:J480),2,FALSE)))

65.9642857142857 & 117.285714285714

[HansV]

You could use

=IFERROR(CONCATENATE(ROUND(MAX(J377:J480),0)," & ",VLOOKUP(MAX(J377:J480),J377:K480,2,FALSE)),"")

and

=IF(COUNT(K377:K480)=0,"",CONCATENATE(ROUND(MIN(K377:K480),0)," & ",VLOOKUP(MIN(K377:K480),CHOOSE({1,2},K377:K480,J377:J480),2,FALSE)))

If you want to suppress decimals in the number after & too, use

=IFERROR(CONCATENATE(ROUND(MAX(J377:J480),0)," & ",ROUND(VLOOKUP(MAX(J377:J480),J377:K480,2,FALSE),0)),"")

and

=IF(COUNT(K377:K480)=0,"",CONCATENATE(ROUND(MIN(K377:K480),0)," & ",ROUND(VLOOKUP(MIN(K377:K480),CHOOSE({1,2},K377:K480,J377:J480),2,FALSE),0)))

[hlewton]

Thank you Hans. I'm not sure what the first set of formulas does but the numbers were still there after the decimal point. The second set of formulas did the trick and eliminated the numbers after the decimal point. Again thank you.

[hlewton]

Hans I am trying to use your formulas as an example to update other spreadsheets. I was doing fine until I ran into the COUNT function. I was using the second set of formulas you created in a couple posts above but can't get them to work with the COUNT function. Here is an example what I tried but it does not work:
=IF(COUNT(C3:C39)=0,"",CONCATENATE(ROUND(MAX(C3:C39),0)," & ",ROUND(VLOOKUP(MAX(C3:C39),CHOOSE({1,2},C3:C39,B3:B39),2,FALSE),0)),"")

How do I use the ROUND function to 0 decimal points in the 2 formulas below?


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



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

[HansV]

The ,"" at the end are superfluous - you had that in the IFERROR forrmula, but this formula doesn't use IFERROR. So:

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

[hlewton]

Thank you again