Help With Excel Formulas

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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
Regards,
hlewton
hlewton

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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?
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?
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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")
=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")
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
=(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.
=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.
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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.
=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.
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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.
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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"
=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"
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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.
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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";;
=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";;
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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?
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?
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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="","",B6A6+1)
That looks clearer to me.
I would write the formula as
=IF(B6="","",B6A6+1)
That looks clearer to me.
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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
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
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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)))
=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)))
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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.
Regards,
hlewton
hlewton

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
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)))
=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)))
Regards,
hlewton
hlewton

 Administrator
 Posts: 72002
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
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)))
=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)))
Regards,
Hans
Hans

 GoldLounger
 Posts: 2814
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA