[Solved] Return text unless value is positive

[Griff]

I am using the current formula and it's been working great, it displays the value I am looking for:

=(D33-C33)/(COUNTIF(E3:E33;""))

I have been trying to create a formula that will show the value of the formula above, unless the value is less than or equal to 0, then I want it to display text "FINISHED". But I cannot for the life of me work out how to do this.

Any help much appreciated.

[FJCC]

Try

Code: Select all

=IF((D33-C33)/(COUNTIF(E3:E33;"")) > 0; (D33-C33)/(COUNTIF(E3:E33;"")); "Finished")

[Griff]

Yes!

Thank you so much. I didn't even think about greater then 0. That works!

[Griff]

Ah, no. If all of the cells E3:E33 are not blank, then it returns a #DIV/0! error.

Need a different formula, and also to work out how to get this thread unmarked as solved.

EDIT: Managed to work out how to unmark this thread as solved. Still trying to work out a formula though. Any help very much appreciated!

[robleyd]

There should never be a negative count of non-empty cells (famous last words) so perhaps

Code: Select all

IF( (COUNTIF(E3:E33;"") = 0;"Finished";(D33-C33)/(COUNTIF(E3:E33;""))

Untested!

[Griff]

I couldn't get that formula to work.

I have added a row into the sheet that doesn't get used into the formula so that the count can never be 0 for division. It's rough workaround, but can't get the formula smooth enough.

Is there any way to share the sheet so people can dig into it to look?

[MrProgrammer]

Is there any way to share the sheet so people can dig into it to look?

If you had read the Survival guide for the forum you would not have to ask.

I have been trying to create a formula that will show the value of the formula above, unless the value is less than or equal to 0, then I want it to display text "FINISHED"

A conditional format can show the formula value as FINISHED instead of a as number. Read about conditional formatting in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum.

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach. Show the case where the formula returns the correct value, but you want to see FINISHED instead of a number. You can use your original formula, not your rough workaround.

I am using the current formula and it's been working great, it displays the value I am looking for: =(D33-C33)/(COUNTIF(E3:E33;""))

Ah, no. If all of the cells E3:E33 are not blank, then it returns a #DIV/0! error.

To avoid a #DIV/0! error, any formula which uses the division operator must ensure that the divisor cannot be zero. What cell on what sheet are you asking about? Explain what the result should be for that cell if the divisor is zero. I cannot help you without that information.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Griff]

Thank you for your response, I had indeed missed the link to attaching files in the Survival Guide.

I have attached a sanitized section of the spreadsheet to use as an example.

The formula in question is in cell D41.

The outcome of this formula should show the average daily total required to hit monthly target(D37) with only the days remaining in the month, while showing "FINISHED" if the target has been hit or exceeded.

I thought I had managed to work it out, but I cannot get around dividing the remaining total by the number of days left in the month, as that remaining number of days will be 0 on the final day of the month and I'm stuck in #DIV/0! territory there.

The formula in place currently will display the wrong average, it was a mistake that I thought I could make work.

Any help much appreciated.

[FJCC]

I think this will work for you.

Code: Select all

=IF(C37 < D37; (D37-C37)/(COUNTIF(E3:E33;"")); "FINISHED")

[Griff]

The above formula will produce a #DIV/0! error if target is not hit by end of month.

I was attempting to have a formula in that cell that would work for different months as they would have different total days, but that might be beyond me. If anyone has any bright ideas on how to accomplish that then please do let me know.

For everyone who has helped so far, thank you very much

[lader]

Maybe like this:

Code: Select all

=IF(C37>=D37;"FINISHED";(D37-C37)/MAX((COUNT(A3:A33)-COUNT(B3:B33));1))

[Griff]

Thank you. I'll use that and mark this as solved.

My quest to make it work for any month will have to continue elsewhere as it is outwith the original parameters of my query.

Thank you all.

[MrProgrammer]

Explain what the result should be for that cell if the divisor is zero.

The above formula will produce a #DIV/0! error if target is not hit by end of month.

I asked you what you wanted to happen when the divisor is zero and didn't get an answer, so I used formua =IF(C36;ROUNDUP(C35/C36);"Goal not achieved") in 202408091734.ods. The average is undefined when no days remain. Providing a number for the average in that situation is nonsense. Any number is bogus.

My attached spreadsheet uses conditional formatting and styles. Styles are required for conditional formatting. Conditional formatting
• in C2:E32 eliminates all of your IF(ISBLANK();…;…) tests,
• in E35 supplies Finished when needed, and
• in E2:E32 shows negative differences with a red background.
To see what happens at the end of the month put 39 in every cell in the range B10:B32. After viewing that result put 38 in every cell in that range.

202408091734.ods

(19.48 KiB) Downloaded 42 times

My quest to make it work for any month will have to continue elsewhere as it is outwith the original parameters of my query.

If you make August in A1 a date intead of text, Calc knows how many days are in that month. You can apply a style to the cell so it only shows the month name, or optionally the month and year. Formulas in A2:A32 can show only the days in that month. Create a new topic if you need help with that idea. I will not help unless you attach your spreadsheet. Read section 3. Dates in cells in Ten concepts that every Calc user should know.
[Tutorial] Calc date formulas