[Solved] Prevent formula result from changing

[Nellap]

I have a simple spreadsheet for people input electricity daily and it calculates the cost on that day. my problem is that the next days input alters all the previous days cost cells and I want to keep the original as they were for reference. I realise this is because all the 31(days in a month) cells hold the same cell number but as the cost calculation has only one result cell I do not know what else to do.
The cost results should read over 3 days .... 28.14, 21.81,and 13.38 but it actually shows 13.38, 13.38 and 13.38.
What I would like to ask is can I switch a formular off once it has been used or is there some other way of protecting each cell from being altered.
You are welcome to see my spreadsheet but I do not know how to down load it to you yet.
Many thanks in advance for any help you are able to pass my way

[robleyd]

Possibly you need a separate column with sub-totals. Please upload a sample file with some dummy data for us to look at.

[Nellap]

Thankyou for your prompt response. I am going to attempt to upload my spreadsheet now Thankyou

Electricity Bill Calculation backup.ods

(11.22 KiB) Downloaded 82 times

Edit: Moved your post into the relevant topic rather than it being in an unrelated tutorial topic.

[FJCC]

Does this version work for you?

[MrProgrammer]

You are welcome to see my spreadsheet but I do not know how to down load it to you yet.

Hi, and welcome to the forum. Thank you for the attachment.

What I would like to ask is can I switch a formula off once it has been used or is there some other way of protecting each cell from being altered.

Yes, this is done by converting the cell's formula to its value. After selecting the cell use F2 → F9 → Enter → Enter. However this will be inconvenient for this task and you don't want to use that method here. The method FJCC suggested will be much better. However, I will offer an alternative which uses the Multiple Operations feature. I think it is not used much because there are not many examples for this feature, but your situation does suggest to me that it can be appropriate here. Please refer to my attachment as I explain how the feature works.

In columns G through J the sheet calculates the price in J10 based on the electricity usage in J9. You can see this in action by typing 100 or 200 in J9. The Multiple Operations feature will apply this calculation method to each day of the month. The attachment has applied the feature to cells D4:D33. I'll explain how I did that. Select those 30 cells and press the Backspace key to remove the formulas so you can rebuild them.

Select cells C4:D33 (30 rows, 2 columns). C is the column with your daily usage values. D is the (currently empty) column where the feature will create formulas for you. Data → Multiple Operations → Formulas → Click cell J10 → Column Input Cell → Click cell J9 → OK. Do not provide a Row Input Cell. You will see that Calc has created formulas for you in D4:D33. The formula in D4 is =MULTIPLE.OPERATIONS(J$10;$J$9;$C4). It means: If cell J9 were set to the value in C4, return the value that would be in J10.

202409210952.ods

Example of using Multiple Operations feature

(18.2 KiB) Downloaded 95 times

You will note that C7:D33 do not show any values. They are hidden by conditional formatting because there are no readings yet in B7:B33. I clicked cell E33, held the Shift key, clicked cell C4, and released the Shift key. This selects C4:E33 and leaves the active cell as C4. Format → Conditional Formatting → Formula is → ISBLANK($B4) → Cell Style → Hide → OK. You must use Formula is, not Cell value is. You must use $B4, not B4. Format → Styles and Formatting will show you how style Hide is arranged with Format Code [>0]"";[<0]"";"";"" in the Numbers portion of the style settings dialog. Read about conditional formatting in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum.

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.

[Nellap]

Fantastic, really neat with no messy nested IF formulars. But I am confused where you got the cost calculation result from as the J cells are blank. The calculations were perfectly correct but from where? you refer in your formular to J9;J10 but one is blank and the other has a value of 7.06
Sorry if I am not seeing the obvious but I tried all ways to find the values in columns I and J.
Thankyou for what you have already done and so quickly. You have given me a new formular to explore.

[MrProgrammer]

You refer in your formular to J9;J10 but one is blank and the other has a value of 7.06

J9 is not blank. It contains a 0. The components of the cost, based on the value in J9, are in column I. Their sum is in J10.

• C4 has a value of 150. Type that in J9. You'll see that J10 becomes 28.14. =MULTIPLE.OPERATIONS(J$10;$J$9;$C4) in D4 asks Calc to pretend to do what you just did manually, then get J10's value.
• C5 has a value of 105. Type that in J9 and you'll see that J10 becomes 21.81. =MULTIPLE.OPERATIONS(J$10;$J$9;$C5) in D5 asks Calc to act as if J9 had a value of 105 and get what would be in J10 if that had happened.

It may help if you read my earlier post again, thinking carefully about my description of how the Multiple Operations feature works. I think that the values in J9 and J10 don't actually change in the spreadsheet when MULTIPLE.OPERATIONS(…) is performed. The cells just provide a pattern for Calc to use, sort of like a subroutine in many programming languages. Don't use this method if you don't understand it. The method FJCC suggested is a more typical way to organize your calculations.

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

[Nellap]

You are right Mr, Programer I do not yet understand the Multiple.Operations formular but I will give it a really good go.
I am reluctant to put solved on yet, although it is working better than I had hoped, as there was another possible solution from FJCC that may be of help.
If I do not hear from him I will put solved on and use your suggestion if that is OK with you.
Again many thanks for streamlining my program and being very clear on how you did it. Nellap

[FJCC]

there was another possible solution from FJCC that may be of help.
If I do not hear from him I will put solved on and use your suggestion if that is OK with you.

I'm not sure what you are waiting to hear from me. Have you tried my solution and is there any problem with it?

[Nellap]

Sorry FJCC completely missed your down load example, thankyou for taking the trouble to show me.
It is one that I can understand a lot easier and will use until I get used to the more complicated functions.
Thank you all for your help and quick response. Nellap