Show blank cell, until entries in another row of cells

[Roger1]

Hi!
I have in a cell(K18) a formula that calculates time based on speed. The speed is found as the highest in the row K5 to K12. The speed again is calculated from info that is written in the cells J5 to J12, also J21 and C7.
I want the cell(K18) in which this formula is located to show blank cell, when there is no entries in the row J5 to J12. When there is entries somewhere in this row, it shall show the result. It works if I have only one cell, J5, in formula, but it does not work if I write the row J5:J12 instead. Then it shows #DIV/0!.

The problem is J5 in the (ISBLANK(J5)). When I try J5:J12 instead the cell is not blank, but it is blank when I write only J5.

I need help to solve this. On forehand I thank You for the time and effort.
Yours Roger in Norway

cell(K18) =IF(OR(ISBLANK(J5));"";(((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7))

[popipipo]

Please Login or Register  to view this content.

Maybe this??

[Roger1]

Thank you for a quick answer, mate.
It almost works. The problem with the row J5:J12 is solved. But now, when I have no entry in J21, and I have entry in the row, the value from C7 comes in and shows up in K18.
I need the cell K18 to be blank until there is entries in both the row J5:J12 and cell J21 at the same time. If there is entries in only one of them, the row J5:J12 or J21, I need the cell K18 to still show blank.

I am sorry to be unclear about this earlier, but I did know it would be like this until I tried Your nice formula.

[popipipo]

upload your file please then we we look at it.

[Roger1]

Hi again!
I dont know if it would be of any help to upload, as all language in the sheets are in Norwegian.
Now we are in this situation: have two formulas that work. We need somehow to merge them.

Nr.1 =IF(COUNTA(J5:J12)=0;"";((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7) works with the row J5:J12 but not cell J21
Nr.2 =IF(OR(ISBLANK(J21));"";(((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7)) Works with cell J21, but not with the row J5:J12

If there is entries in only one of them, the row J5:J12 or J21, or in neither, I need the cell K21(moved it) to still show blank. I need the cell K21 to show the value only when it is written values in the row J5:J12 and cell J21 at the same time.

Do you still want me to upload file? If so, is there any way to do that to you personally? Grateful for Your time and effort, mate.

[popipipo]

Please Login or Register  to view this content.

Try it this way

[Roger1]

Hi!
I am sorry, it didnt work.
With the formula =IF(COUNTA(J5:J12;J21)=0;"";((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7) this happends:
-Nothing written in the row J5:J12 or the cell J21 : cell K21 show blank
-nothing written in the row J5:J12, but have entry in cell J21 : cell K21 show #DIV/0!
-entry in row J5:J12, and nothing in cell J21 : cell K21 show the value from C7. (C7 is a time, manually filled in, for example 11:00:00(not using AM/PM in Norway))
-entry in both the row J5:J12 and cell J21 : cell K21 show the calculated value as it should

When using the formula =IF(COUNTA(J5:J12)=0;"";((J20*1000)/MAX($K$5:$K$12)/1440)+$C$7) without ;J21 this happends
-Nothing written in the row J5:J12 or the cell J21 : cell K21 show blank
-nothing written in the row J5:J12, but have entry in cell J21 : cell K21 show blank
-entry in row J5:J12, and nothing in cell J21 : cell K21 show the value from C7. (C7 is a time, manually filled in, for example 11:00:00)
-entry in both the row J5:J12 and cell J21 : cell K21 show the calculated value as it should

When using formula: =IF(OR(ISBLANK(J21));"";(((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7)) this happends :
-Nothing written in the row J5:J12 or the cell J21 : cell K21 show blank
-nothing written in the row J5:J12, but have entry in cell J21 : cell K21 show #DIV/0!
-entry in row J5:J12, and nothing in cell J21 : cell K21 show blank
-entry in both the row J5:J12 and cell J21 : cell K21 show the calculated value as it should

Cell K21 is made to show a calculated time, for example 14:03:16

Generally for me it seems difficult to combine a row and a single cell in the same formula in Excel. As you see above one formula has one error, and another formula have another error, but else good. I dont know how to combine these, so it works as intended. And we cant have two formulas in one cell either. I hope I dont have to make a macro, if so, I am stuck.

In this workbook I am trying to make a "calculator" for racing pigeons, that can be used during the race by the racing pigeon fancier and showing many calculations during race. Making it more exiting. I have searched the web, but have not seen anything like what I am trying to make. The fomulas we are looking at now is one of the calculations in the workbook.

Again, thanks o7

[popipipo]

please upload a file with values and results you want.

[Roger1]

Hi!
I am not so keen to upload my file, so that it is free for everybody to download it. It is also very unfinished and in Norwegian Language. But I have no problem with sending it to you in a way that only you get it. If that is possible, please tell me how.

We have three formulas that almost work:
in cell K21 =IF(COUNTA(J5:J12;J21)=0;"";((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7)
in cell K21 =IF(COUNTA(J5:J12)=0;"";((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7)
in cell K21 =IF(OR(ISBLANK(J21));"";(((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7))

What I want is this:
-Nothing written in the row J5:J12 or the cell J21 : cell K21 show blank
-nothing written in the row J5:J12, but have entry in cell J21 : cell K21 show blank
-entry in row J5:J12, and nothing in cell J21 : cell K21 show blank
-entry in both the row J5:J12 and cell J21 : cell K21 show the calculated value as it should

And:
in cell J21 is distance , f.ex. 239,123km manually written in by user
in row J5:J12 is time, f.ex. 12:15:35 manually written in by user
(in row K5:K12 is speed, f.ex 1235,14 meters per minute calculated by formula )
(in cell C7 is time, f.ex 10:00:00 manually written in by user)

If you can not get further, without me uploading my file openly, then I can try another solution. That is to find a formula or macro that automatically sort the row K5:K12 with highest to lowest value and then use the ISBLANK-formula in cell K21 with two single cells. Like this: =IF(OR(ISBLANK(J21);(ISBLANK($K$5));"";(((J21*1000)/MAX($K$5:$K$12)/1440)+$C$7))
If I shall use a sorting solution; I have 8 horizontal rows starting with I5:L5 and ending with I12:L12. These horizontal rows being sorted only by the value in K- row from K5 to K12. Highest to lowest.

What do you think?
Yours sincerly and gratefully

[popipipo]

If you cant upload a (mockup) file, then I can not help you anymore
sorry for that.