The W
Views: 100264660
Main | FAQ | Search: Y! / G | Calendar | Color chart | Log in for more!
31.10.14 0500
The W - Internet & Computers - Shorter formula in OpenOffice Calc?
This thread has 20 referrals leading to it
Register and log in to post!
Thread rated: 7.00
Pages: 1
(198 newer) Next thread | Previous thread
User
Post (6 total)
drjayphd
Scrapple
Moderator








Since: 22.4.02
From: Long Island

Since last post: 4 days
Last activity: 6 hours
AIM:  
ICQ:  
Y!:
#1 Posted on | Instant Rating: 6.79


You wanted the best, you got... Out of Context Quote of the Week: FROM THE VAULTS.

"I just want the ability to crush a man's neck with my thighs." (JST)


In my usual fashion of using spreadsheets to make my life easier, I'm whipping up a sheet for Restaurant City (hush) to sort out ingredients. Namely, how many I'll need to max out dishes. Using OpenOffice.org 3.1.1 for this, and that makes a difference as far as syntax goes.

In order to do this, I'm using the COUNTIF() function to search for every instance of an ingredient (as in =COUNTIF(B2:E72;"Tomato") to find every time a tomato is needed). Works perfectly for that purpose, and with a way to enter a dish's level, I'll be able to figure out how many of each ingredient I've used. I've found a formula that works for that purpose, but the problem is that there's enough dishes that the formula itself is too long. How long?

=(COUNTIF(B2:E2;"Tomato")*F2)+(COUNTIF(B3:E3;"Tomato")*F3)+(COUNTIF(B4:E4;"Tomato")*F4)+... until you get through all 72 rows. The limit is 512 characters per formula, as far as I can tell, and this way is nearly 2,250.

Any idea how I can shorten that? I just need it to count up every instance of an ingredient in that range, multiply it by another cell's contents, and do that 68 (for now) times. Or a way to circumvent the character restriction would be nice...

EDIT: I should mention that since I list each ingredient individually, replacing the name with that cell's location works, but it doesn't shorten things enough. Breaking it up into separate columns per course does shorten it enough, though, but there's no reason to do that besides to make this work. I'd like a more elegant solution.

(edited by drjayphd on 27.9.09 1959)


Promote this thread!
Mr. Boffo
Scrapple








Since: 24.3.02
From: Oshkosh, WI

Since last post: 459 days
Last activity: 420 days
#2 Posted on | Instant Rating: 5.29
Ah, of course. I use a spreadsheet to figure out which properties give me the best return on investment in Mafia Wars: New York, so I know where you're coming from.

As it happens I also ran into this with my tv ratings spreadsheet. I had a column that figured out the average 18-49 rating for a certain show for the year. Originally I had to hard code it with whatever columns that information was in, i.e. =average(A2,D2,G2,J2,M2,P2...) (the 18-49 rating is in every 3rd column). When that got too long I did some searching on the internet and found a conditional array function that did it.

See http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Using_Arrays for the online documentation on array functions. See especially the Tips and Tricks section which gives some examples of easy things that can be done.

I feel like there's a way to make an array function for this as well, but I can't quite visualize what the expected output of the long formula is. Could you upload the spreadsheet somewhere so that people can take a look at it?
drjayphd
Scrapple
Moderator








Since: 22.4.02
From: Long Island

Since last post: 4 days
Last activity: 6 hours
AIM:  
ICQ:  
Y!:
#3 Posted on | Instant Rating: 6.79
You wanted the best, you got... Out of Context Quote of the Week: FROM THE VAULTS.

"I just want the ability to crush a man's neck with my thighs." (JST)


    Originally posted by Mr. Boffo
    Ah, of course. I use a spreadsheet to figure out which properties give me the best return on investment in Mafia Wars: New York, so I know where you're coming from.

    As it happens I also ran into this with my tv ratings spreadsheet. I had a column that figured out the average 18-49 rating for a certain show for the year. Originally I had to hard code it with whatever columns that information was in, i.e. =average(A2,D2,G2,J2,M2,P2...) (the 18-49 rating is in every 3rd column). When that got too long I did some searching on the internet and found a conditional array function that did it.

    See http://wiki.services.openoffice.org/​wiki/​Documentation/​How_​Tos/​Using_​Arrays for the online documentation on array functions. See especially the Tips and Tricks section which gives some examples of easy things that can be done.

    I feel like there's a way to make an array function for this as well, but I can't quite visualize what the expected output of the long formula is. Could you upload the spreadsheet somewhere so that people can take a look at it?


Well, at the bottom of that page, it says COUNTIF() doesn't work with arrays, so that idea's out, alas. Just made a change that broke most of the formulas, so I need to redo them, but once that's done I'll happily upload it.



Guru Zim
SQL Dejection
Administrator








Since: 9.12.01
From: Bay City, OR

Since last post: 1 day
Last activity: 7 hours
AIM:  
#4 Posted on | Instant Rating: 9.04
Doesn't OO.o support java? That might be an option.




Sign up for Folding@Home and join our team. PM me for details.

Ignorance is bliss for you, hell for me.
drjayphd
Scrapple
Moderator








Since: 22.4.02
From: Long Island

Since last post: 4 days
Last activity: 6 hours
AIM:  
ICQ:  
Y!:
#5 Posted on | Instant Rating: 6.79


You wanted the best, you got... Out of Context Quote of the Week: FROM THE VAULTS.

"I just want the ability to crush a man's neck with my thighs." (JST)


    Originally posted by Guru Zim
    Doesn't OO.o support java? That might be an option.


Yeah, but I don't know any Java, alas. This is also admittedly not the most elegant solution, I'm sure, but here's the spreadsheet (mediafire.com).

It should be noted that all typos are intentional, as the game's programmers aren't known for their spell-checking.

(edited by drjayphd on 28.9.09 1504)


JayJayDean
Scrapple








Since: 2.1.02
From: Seattle, WA

Since last post: 58 days
Last activity: 1 day
AIM:  
Y!:
#6 Posted on | Instant Rating: 7.29
    Originally posted by Mr. Boffo
    Ah, of course. I use a spreadsheet to figure out which properties give me the best return on investment in Mafia Wars: New York, so I know where you're coming from.


Wow, I suddenly feel like so much less of a dork knowing someone else does that. (Mine has the jobs in it, too, so I know which ones have the best energy/exp ratio.)



Holy fuck shit motherfucker shit. Read comics. Fuck shit shit fuck shit I sold out when I did my job. Fuck fuck fuck shit fuck. Sorry had to do it....

*snip*

Revenge of the Sith = one thumb up from me. Fuck shit. I want to tittie fuck your ass.
-- The Guinness. to Cerebus
Thread rated: 7.00
Pages: 1
Thread ahead: So, I bought the family a new computer...
Next thread: moving iTunes library
Previous thread: NotMyIP?
(198 newer) Next thread | Previous thread
Hi guys, need some help I got a little upset at spyware that kept redirecting my browswer on my laptop and punched the screen, which I have done before. Unfortunately, this time the screen cracked and is broken.
- ShotGunShep, Laptop monitor switch? (2004)
The W - Internet & Computers - Shorter formula in OpenOffice Calc?Register and log in to post!

The W™ message board

ZimBoard
©2001-2014 Brothers Zim

This old hunk of junk rendered your page in 0.067 seconds.