[Solved] Need help with cell formulas

Discuss the spreadsheet application
Post Reply
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

[Solved] Need help with cell formulas

Post by Teevo42 »

(SCROLL DOWN TO SEE MOST UPDATED QUESTION PLEASE, THANK YOU!)

Hello all, I'll try to explain what I need as simply as I can.

- I am making a long list of basketball cards. The rows are designated for each individual card, while the columns are for separating them by card grade.
- I have created a points system to award certain point values based upon the tier the card is in, as well as what grade it has.
- What I want to do is to be able to put an "x" in a cell signifying that I own that card in that grade, but for that cell to have a formula hidden within it that will tally up into one cell of my choosing at the very end of the list.

For example, lets say I have card (A).
- Card (A) is graded 9 so it gets 4 points
- Card (A) falls under tier 2 so it gets a multiplier of times 4
- 4 x 4 = 16
- I want to be able to put an "x" in the appropriate cell to show that I have that card in that grade, but I want that cell to have the formula assigned to it hidden in the background, and I want that total (16 in this case) to show up in a cell at the end of my list.
- I want the cell to be blank unless I choose to put an "x" there, and the "x" is the only thing I want to see (aka no number total from the formula hiding in the background)

Can anyone help?
Last edited by Teevo42 on Thu Feb 22, 2018 7:19 am, edited 3 times in total.
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need help with cell formulas

Post by Zizi64 »

If I understood your task exactly: You want to use a formula with existing, but unvisible results in the rows, because you want sum the unvisible results. And - I suppose it - you want ti make visible/unvisible the results freely.

A tip for making visible/unvisible an existing result of a formula: Use the conditional format feature with a White-background/White-character Cell Style and with a normal Cell Style.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

I have attached a screenshot of what my spreadsheet looks like. I randomly put some "x" in there to show you thats what I want to be able to do... which is put an x in a cell (which conveys that I own that card in that grade) but I want that cell to have an assigned value/formula to it. Like 2 times 5. But I want that to be hidden, and I want that value (which is 10 in this example) to be linked to a cell at the end of my list which will tally up all the cells that I have marked with an "x".
Attachments
ex.PNG
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need help with cell formulas

Post by robleyd »

You can't have a formula and user entered data in the same cell. You might be able to achieve something similar by having a helper column for the formulae, which you could perhaps set as a hidden column.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need help with cell formulas

Post by Zizi64 »

You can't have a formula and user entered data in the same cell. You might be able to achieve something similar by having a helper column for the formulae, which you could perhaps set as a hidden column.
Yes, it is true. But you can format the result of the formula with a user defined number format by format code: "X".

Here is a small sample:
Cell containing a formula-result-string.ods
(8.63 KiB) Downloaded 76 times
You must put a formula into the formatted cells (They are formatted by the Cell Style named "CellStyleX".)
The result of the formula will be hided by the X character, but you can use the numeric results for other calculations.



But: in my opinion, it is better to achieve this task with usage of some helper columns (tables) for the calculations.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

The helper columns (which will be hidden) sound like the best route to go. Can you help me do the first cell, and then I can do the rest? I want cells E2 through E19 to have the value 10 assigned to them. Whether we do that by just using the number 10 or by including the 2 times 5 multiplication formula does not matter.
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need help with cell formulas

Post by Zizi64 »

Can you help me do the first cell, and then I can do the rest?
Please upload your real ODF type sample file with some data. Sorry i not understood, what are the input values of the calculations, where (which cells) they come from?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

Sent you a private message.
Open Office 4.1.4 / Windows 7 HP 64bit
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

I've figured out quite a bit with the help of Zizi. Here is where I'm stuck now:

I want to be able to select an area of cells (like G24 through G55), and input data only into cells that currently have an "x"... leaving all the empty cells still empty, but filling in each "x" cell with the number 12.

I feel like I'm making this sound confusing...Ultimately I want to input the same data (the number "12") only into the cells that currently already have data in them (in this scenario, the cells will already have an "x"), and I want to be able to do this all at once by selecting a specific area of cells (in this case, G24 through G55). Does that make sense?
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Need help with cell formulas

Post by keme »

Select range
Menu item Edit - Find and replace...
Search for x
Replace with 12
Tick boxes as required to differentiate between upper and lower case (replace x but not X) and only regard entire cells (replace x when it is a single character, but not when there are more text in the cell)
Click to display more options
Tick to search/replace in selection only.
Click Replace all
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

I can't believe I forgot about find and replace. Let me try this when I get home from work. I'll let you know if I get stuck, thank you! Standby..
Open Office 4.1.4 / Windows 7 HP 64bit
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

Thank you Keme, that was it. Still feel so silly for not thinking about Find & Replace (face-palm). And thank you zizi for all your time and help.

I have one (hopefully last?) question... is there a way to highlight an area of cells and find out how many cells have data in them?
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Need help with cell formulas

Post by RusselB »

The following is based on the layout of my system, which uses the US English language.
First step is to make a small change to your display. The item you are changing in located at the bottom of your screen just to the left of the Zoom setting (which is at the bottom right of the screen).
You probably see the word SUM in that location
Right click on the word SUM and select COUNTA from the list.
Next highlight the area you are interested in using the shift key and arrow keys on the keyboard, or the shift key with the mouse.
The display for the COUNTA will count the number of cells that have entries, irrelevant as to whether those entries are text, numeric or results of formulas,
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: [Solved] Need help with cell formulas

Post by Teevo42 »

Awesome, thanks Russel!

Curious... is there a way to incorporate that into a cell formula?

Like if I had L2 through L22 with some cells in that range with data... and I wanted to input a formula into cell L23 that calculates a total number of cells with some sort of data. Is that possible?
Open Office 4.1.4 / Windows 7 HP 64bit
unspoiled
Posts: 10
Joined: Thu Feb 22, 2018 1:07 am

Re: Need help with cell formulas

Post by unspoiled »

Yes, "helper" column makes sense.

You can make these extra columns formatted with a very small width so as to not make your spreadsheet too wide for viewing/printing.
OpenOffice 3.1, WinXP,
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

Thanks for the input Unspoiled. Everything is solved except for my last question from my last post. Hope to get a reply for that one. Everyone has been extremely helpful.
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Need help with cell formulas

Post by robleyd »

Teevo42 wrote:Awesome, thanks Russel!

Curious... is there a way to incorporate that into a cell formula?

Like if I had L2 through L22 with some cells in that range with data... and I wanted to input a formula into cell L23 that calculates a total number of cells with some sort of data. Is that possible?
Yes, and yes. Now it is time for you to do a little research - please see the Help for COUNTA; press F1 and search COUNTA and all will be revealed.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

Theres 7 different help topics, then dozens of sub category topics. Could you tell me which one pertains to what I'm looking to do? I don't mind reading up on something, but I'd rather not try finding something that I have no idea where it is, when I'm sure plenty of people here can inform me of which topic and sub category to read up on.
Open Office 4.1.4 / Windows 7 HP 64bit
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

Change that, I realized I needed to double click it in order for it to bring up the description and example. Unfortunately all it says is this:

COUNTA
Counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0. If an argument is an array or reference, empty cells within the array or reference are ignored.
Syntax
COUNTA(Value1; Value2; ... Value30)
Value1; Value2, ... are 1 to 30 arguments representing the values to be counted.
Example
The entries 2, 4, 6 and eight in the Value 1-4 fields are to be counted.
=COUNTA(2;4;6;"eight") = 4. The count of values is therefore 4.

Maybe I'm just not as computer smart as others, or maybe I'm just not verse enough with the terminology to fully understand... but none of what it says there makes sense in terms of what I would like to do with my last question. Any assistance would be appreciated. Sorry if anyone here is frustrated with my questions, but no one needs to feel obligated to respond.
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need help with cell formulas

Post by robleyd »

The values might be represented by the content of cells; and if you have a contiguous set of cells - an array - you can refer to them as a range A1:A50 rather than A2;A2;A3...A50

Or you may use a named range - a reference - to define a rectangular block of cells in several rows and columns.

So, for the first situation:
=COUNTA(A1:A50)

or in the second case:
+COUNTA(rangename) where obviously rangename would be replaced with whatever name you have used.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: Need help with cell formulas

Post by Teevo42 »

Gotcha. I'm more of a visual-learn-by-doing type of learner, so seeing the formula and applying it makes total sense now. I am going to use the first one, thats exactly what I needed. Thank you Rob, and again... I can't thank everyone enough who pitched in. Stellar community. Marking this as solved.
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [SOLVED] Need help with cell formulas

Post by robleyd »

For future reference, if you have a number of different problems, we ask that you start a new topic for each. Makes it easier for someone coming along later with a similar problem to find a solution if it isn't buried in an unrelated topic.

Assuming of course that anyone actually uses the search feature before posting :lol:
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Teevo42
Posts: 20
Joined: Sun Feb 18, 2018 6:33 am

Re: [SOLVED] Need help with cell formulas

Post by Teevo42 »

Ok, sorry about that. I figured you guys would rather have it condensed rather than 7-8 threads. But will do, np.
Open Office 4.1.4 / Windows 7 HP 64bit
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [SOLVED] Need help with cell formulas

Post by robleyd »

I'm guessing that you haven't yet gotten around to reading the Survival Guide - you can also get to that from the top of the forum index page.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply