If a field is not blank, choose a number between 2 other fields

Question

Level 2

235 points

If a field is not blank, choose a number between 2 other fields

Hello, again.

Firstly my big spreadsheet was a 'running log', then it was the 'walking log' and now is my 'running and walking log'.

I asked for help before and the members "Recycleur" and "Badunit" were generous in helping me, but for reason that I could not figure out, I could not make the solution of "Recycleur" work in its second phase; while the solution of "Badunit" answered my question at the time.

Recycleur's help gave me a thing that I did not ask for, but that I want to keep it ... in an extra column.

Recycleur's help created 2 extra columns, later reorganized.

(i) With the header 'Run', the cells of the 'P column' were formed by the formula: "IF(COUNTMATCHES($D3,P$1)>0,COUNTMATCHES(TEXTJOIN(",",TRUE,OFFSET($D$1,1,0,ROW()−1,1)),P$1),"")".

(ii) With the header 'Hike', the cells of the 'Q column' were formed by the formula: "IF(COUNTMATCHES($D3,Q$1)>0,COUNTMATCHES(TEXTJOIN(",",TRUE,OFFSET($D$1,1,0,ROW()−1,1)),Q$1),"")"

What each of these columns show are the cardinal numbers -- acting as ordinal numbers -- of the 'Run' and of the 'Hike'; so, I would like to have these cardinal/ordinal numbers in the 'C column', since in the 'D column' I have the field 'Activity', and each of the rows have 2 options "Run" or "Hike"; the 'G column' has the 'Date/Time' of the activity.

So the algorithm should do these steps;

"if 'G' is blank, 'C' should obviously be empty";

"if 'G' has a 'Date/Time', then it means that an 'Activity' was registered; so there is an cardinal/ordinal number either in the 'P column' or in the 'Q column'; and this number should be copied into the 'C column'.

If I was doing this on ClarisWorks/AppleWorks I would use the 'or function' and the result would be shown very easily.

But Numbers is harder on the syntax, and I could get only the cardinal/ordinal number of only one of those 2 activities; where I should get the cardinal/ordinal number of the other activity, I got a red air a blue triangle -- depending on the variation of the formula used.

Thanks in advance for any help.

Jorge Lucas (the guy from Rio Grande do Sul)

MacBook Air 13″

Posted on Nov 1, 2023 1:52 PM

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,657 points

Nov 1, 2023 2:51 PM in response to Jorge Lucas

So you want something like that?

In C2

=IF(G2="","",MAX(P2:Q2))

Reply

Answer 2

Recycleur

Level 4

1,657 points

Nov 2, 2023 3:49 PM in response to Jorge Lucas

Hi Jorge

After giving my answer yesterday I started thinking about the bigger picture here. If you may, I'll suggest something that us above the forest can see that you can't because you're in it and only see the trees.

You started your spreadsheet to track your runs. Then you added your hikes in the mix, all interspersed, with all kinds of complicated formulas to count each other. Now this configuration prevents you from making separate charts per activity to follow the progress of speed, duration, distance, etc. Moreover, should you eventually take on more activities like cycling, rowing or swimming, incorporating them in your activity table would become a nightmare.

If that suits your tracking goals my suggestion would be to return to your original model and make a duplicate of the sheet for each activity, each with its own set of identical tables and charts (who doesn't like charts?). You could have a simple summary sheet to visualize key values from each activity, like the number of times, the number of days since the last outing, the total distance over a year, etc.

Reply

Answer 3

Jorge Lucas Author

Level 2

235 points

Nov 2, 2023 5:39 PM in response to Recycleur

Hello Recycleur,

I love to run, but since January of 2022 I started to walk -- in a very intense pace and for around 1 hour -- as a precaution for the heart rate and blood pressure that go up during the runs.

After 20 months of these hikes and doing some exercises that promotes the dilation of blood vessels, I started to do a hike and after each hike I am doing a run.

In November and December, I should be running only 2 km; I intend to increase a 400-m lap in my runs, each 2 months or so in my runs.

If the things go well, I will be running 5 km ~ 6 km at the end of next year.

And if the things go really well, I will quit hiking.

So, I am starting a transition.

I already have monthly summaries of the runs and the hikes, and I also have annual summaries for both the activities -- in other sheets of the same document.

I can also add 2-D charts with both activities in the same chart, and I almost did it when I was only walking.

I abandoned that idea because although my speed was very high, it also very flat; so, it would be almost an horizontal line. But including the runs it could be interesting -- and I would have to choose a way that brings me some immediate reading of the data shown.

Cycling is not on my radar because in the city that I live there is no cycle lane of concrete/asphalt around my place. In the club, it would be boring around a 400-m track. And I like bicycle with thin tires -- that I had more than 45 years ago; today I do not have one.

Swimming I used to practice when I lived by the sea; years later, where I live now, I used to swim in a 25-m swimming pool, but because of the elders -- that asked for a temperature around 22 ºC or more --, I gave up. I have no technique to swim, and some years ago I was swimming 1 km.

When I lived by the sea, I used to play football at the beach, but I was never a good player, not even so so; but I liked to play on the beach. In the grass, I never had a football boot.

And football is a collective sport, of 11-man teams.

So, my interests are only in the track field.

Having said this, I must say that your suggestion surprised me -- specially because I suppose you are an American, and Americans are more constrained than Latin Americans.

I already thanked you before for the help in the spreadsheet, and I appreciated very much for the suggestion about a bigger picture.

Jorge Lucas

Reply

Answer 4

Jorge Lucas Author

Level 2

235 points

Nov 1, 2023 3:56 PM in response to Recycleur

Recycleur,

You nailed it; better: you bit it!

Thanks,

Jorge Lucas (the guy from Rio Grande do Sul)

Reply

If a field is not blank, choose a number between 2 other fields

Similar questions