Computing desk
< May 6	<< Apr \| May \| Jun >>	May 8 >

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

May 7

How do I translate this math equation into an Excel formula?

How do I translate this equation into an Excel formula? Below is the equation. And below is my Excel formula. But my formula does not seem to be working. What did I do wrong?

Here is the equation:

${\text{Gallon}}=14.9630*{1 \over 2}\left(\left(h-r\right){\sqrt {1-\left({h-r \over r}\right)^{2}}}+r\sin ^{-1}\left({h-r \over r}\right)-{\pi r \over 2}\right)$

The radius (r) equals 24.01, so I just typed in 24.01 into cell A1. So, in my formula, I use a reference to the value of CELL A1 instead of "r" throughout the formula.

And the height (h) is represented by the value in CELL A12. So, in my formula, I use a reference to the value of CELL A12 instead of "h" throughout the formula.

Here is my Excel formula:

=14.963*(1/2)*((A12-A1)*SQRT(1-((A12-A1)/A1)^2)+(A1*ASIN((A12-A1)/A1)-((PI()*A1)/2)))

Thanks. Joseph A. Spadaro (talk) 01:11, 7 May 2016 (UTC)[reply]

Your formula is missing.. Bytesock (talk) 01:09, 7 May 2016 (UTC)[reply]

What do happen when you try it? btw, I would test SQ(2.5), (2.5)^2, PI() just to see if those work as aspected. Bytesock (talk) 02:22, 7 May 2016 (UTC)[reply]

(1) What? I did not understand your suggestion at all. (2) What happens is that I get an incorrect result/value. Joseph A. Spadaro (talk) 02:33, 7 May 2016 (UTC)[reply]

Looks like you have one of the closing parentheses out of place: Try:

=14.963*(1/2)*((A12-A1)*SQRT(1-((A12-A1)/A1)^2)+(A1*ASIN((A12-A1)/A1))-((PI()*A1)/2))

-- Tom N talk/contrib 04:24, 7 May 2016 (UTC)[reply]

Thanks. But, no. Your formula gave me the same exact results as did my formula. In my formula, if I enter the value "8" in Cell A12, I get a resulting value of negative 502.5414 from the formula. Your formula produces the exact same result. The correct result should be approximately positive 61 or so. For what it's worth, this question is related to another question that I posted on the Math Reference Desk. Here: Wikipedia:Reference desk/Mathematics#What kind of function does this data represent?. Thanks. Joseph A. Spadaro (talk) 04:48, 7 May 2016 (UTC)[reply]

I now see that the misplaced parentheses only changed the additive grouping, with no net effect. I did some digging on the web and found a better formula, which I've posted on the Mathematics desk. -- Tom N talk/contrib 07:54, 7 May 2016 (UTC)[reply]

My first chekc would be whether your formula expects the inverse sine function to work in degrees or radians. Excel works in radians.--Phil Holmes (talk) 11:41, 7 May 2016 (UTC)[reply]

Of course it's radians — any measure of length/area/volume (and almost anything posted as serious mathematics — e.g., on the Math Desk) is naturally expressed therein (and there's a

\pi r/2

next to the arcsine that is obviously an offset to its (radian-based) value). --Tardis (talk) 15:47, 7 May 2016 (UTC)[reply]

One suggestion to avoid mistakes is to break such a complex formula up into pieces. For example, you could put this in one cell then use it later to build the full formula:

${\sqrt {1-\left({h-r \over r}\right)^{2}}}$

You could even break up repeated expression, and assign them their own cells, like this:

${h-r \over r}$

Or even this:

${h-r}$

This makes it easier to debug, as you can look at each component of the formula, and figure out if it is calculated correctly. StuRat (talk) 15:43, 7 May 2016 (UTC)[reply]

Yes, I understand that reasoning and that approach. However, for me, that only makes the spreadsheet more confusing, more cluttered, and more difficult to understand. Joseph A. Spadaro (talk) 21:12, 7 May 2016 (UTC)[reply]

Myself, I always seem to get my opening and closing parens mismatched, so it helps me to break it up. As far as appearing cluttered, I believe you can hide the row or column containing those cells once you no longer need to see them. StuRat (talk) 21:22, 7 May 2016 (UTC)[reply]

Yes, the open and close parentheses are always a problem. One that is hard to pinpoint. As far as "hidden" stuff in a spreadsheet, that makes things for me even more confusing. I need to see everything directly in front of my eyes to "follow" how I created formulas and so forth. Joseph A. Spadaro (talk) 03:41, 8 May 2016 (UTC)[reply]

Even if you want to have the final version as one big formula, I find that temporarily breaking it up can help with debugging. Once you have got it working, you can re-combine the parts into one equation. Iapetus (talk) 08:22, 11 May 2016 (UTC)[reply]

Joseph, Did you try the new formula that I posted on the math desk? ~~The earlier calculation just doesn't seem right to me, having apparent inconsistent dimensionality - the first inner part calculates area, but the second and third parts calculate length.~~ -- Tom N talk/contrib 21:36, 7 May 2016 (UTC)[reply]

Thanks. No, I didn't get to that yet. But I will check it out. Thanks. Joseph A. Spadaro (talk) 03:43, 8 May 2016 (UTC)[reply]

Turns out, the formula had a sign error. The following corrected version should give you the expected results:

=14.963*(1/2)*((A12-A1)*SQRT(1-((A12-A1)/A1)^2)-(A1*ASIN((A12-A1)/A1))-((PI()*A1)/2))

More details on Mathematics desk. -- Tom N talk/contrib 03:21, 8 May 2016 (UTC)[reply]

Thanks, all. Joseph A. Spadaro (talk) 12:12, 9 May 2016 (UTC)[reply]

Can I edit ODK records online?

Can I manipulate Open Data Kit databases online? I can't find anything about editing (or removing or creating with un-real-timestamps) records, suggesting to me that it can't be done. (I'm working in KoBo Toolbox.) Perhaps it's part of a philosophy that anything and everything the collection forms collect must be carved in stone? Of course I can import the database somewhere and process it, but if, say, my first few records were tests, I'd have to delete them after every import, or build a work-around. Hayttom (talk) 16:47, 7 May 2016 (UTC)[reply]

Honest, I didn't stage this: I just went back into the Data View screen and suddenly noticed the View Submission (tiny) icon. It brings up records with Edit and Delete options. While I can't access the timestamps, it's good enough for my purposes. Hayttom (talk) 17:13, 7 May 2016 (UTC)[reply]

Resolved

Why won't the entire internet be LAN

Hi there,
I've read about routers and switches, but I haven't figured it out why won't the entire internet be a LAN.
What are the benefits by using multiple protocols and gateways?
Another thing is why there are MAN, I mean why won't a university use a giant LAN instead?
And why a big organization uses a WAN and not LAN instead?
Thanks! — Preceding unsigned comment added by Exx8 (talk • contribs) 17:45, 7 May 2016 (UTC)[reply]

In a LAN, a single device (the layer 2 switch) knows about all the devices attached to it (in practice it keeps a map correlating ethernet MAC addresses with physical network ports). When new devices are added, or existing devices are disconnected, the switch updates its table - so it can instantly direct packets to their intended destination. Keeping that table, and keeping it up to date, is tractable when there are a few dozen, or a few hundred, devices in the network. But the internet has hundreds of millions, and soon to be billions, of devices. If a single doohickey had to keep individual track of all of those it would be gigantic, would be overwhelmed by traffic, and would be a huge single point of failure. So routeable protocols like IP, and the Internet built with it and systems like BGP and DNS, allow knowledge of distant devices, and the networks to which they're connected, to be delegated to network infrastructure near to those devices. -- Finlay McWalter··–·Talk 19:57, 7 May 2016 (UTC)[reply]

Not enough IPv4 addresses avail. And no, not every evil kid should directly access my or Your computer. --Hans Haase (有问题吗) 20:24, 7 May 2016 (UTC)[reply]

Why the whole world isn't directly wired to a gigantic direct network is because it would demand a lot of resources to keep track of all the nodes and traffic would collide. So networks are split up into several smaller networks that are interconnected hence, inter-net. In practice the local network keeps track of the identity of every physical node (MAC-address + ARP) and when a computer sends something. Other has to wait for their turn, this is called collision domain. On this very basic level node addresses and where they are kept track of by all nodes and switched connected to it. But once you want to reach a computer outside of your network. A computer with physical node presence on more than one network (Router) has to strip the node address (MAC) and make use of a higher level address, ie the Internet address (IP). So "switches" and "hubs" knows only about physical local nodes. While routers know about which network a specific node can be found at. When you need to reach a node that is not on a network that any router connected to your network knows about. It has to been sent a table of the general direction of where the packet should go (BGP). The rest is mainly a question of latency demands, throughout demands, computing power per node and economics. So your local super-LAN could be done if every switch had gigantic with memory and processing power and the latency and speed where dismal, despite gigagillions of bits per second. Some smart people figured it were more optimal to split networks at the level when the lookup time and hence latence and the cost of computing power per router reached the point of diminishing returns. Or that the local network were too busy with nodes that actually didn't talk to each other. The rest like WAN, MAN, etc is a mix of human concepts and marketdroid talk. Bytesock (talk) 18:08, 8 May 2016 (UTC)[reply]

Some of this goes back to the 60's. Arpanet (which is where the Internet came from) was a military network. I worked in radar communication networks. The goal of Arpanet was that if you bombed a switch or router or cut a cable, you wouldn't down the entire network. For the Internet, the same goal was kept. If a big switch went down, the Internet would keep working by rerouting traffic through another switch. In theory, it still works that way. In reality, much of the communication on the Internet has been consolidated so there are many places where a single point of failure can cause catastrophic Internet failure. 199.15.144.250 (talk) 14:53, 9 May 2016 (UTC)[reply]

Hmmm... got a citation to support that last claim? The other bits are covered by Arpanet, Internet, decentralization and network robustness. SemanticMantis (talk) 20:05, 9 May 2016 (UTC)[reply]

How about domain name server. If that fails, most people cannot functionally use the Internet. The Internet isn't down, but domain name resolution fails. Since people don't memorize IP addresses, the Internet is effectively down due to a single point of failure. When I used Comcast, we regularly had complete failure of their DNS and I had to edit the resolv file to point to Google's DNS to get things working. Most people can't do that. They just know the Internet is down. 209.149.115.229 (talk) 19:24, 10 May 2016 (UTC)[reply]

Set up your own DNS server and be done with the crappy, censor and spy ISP one. Bytesock (talk) 00:30, 12 May 2016 (UTC)[reply]

I remember when a switch in Atlanta went out and nobody I knew could access much of the Internet. We were on different Internet Service Providers. I had Comcast. At work we used AT&T. My phone was Sprint and it had a data plan. For about half a day (it started before I woke up), most of the Internet was simply gone. Traceroute said that the packets went to Atlanta and then vanished. Googling for "massive internet outage" brings up many news articles discussing outages. An interesting one took place in Australia. A working accidentally DDOS'd a main switch and downed most of the country. I find it interesting that a single user can take down such a large network by accident. 47.49.128.58 (talk) 09:56, 12 May 2016 (UTC)[reply]

What people should find a wider interest in is the incompetency on how the infrastructure is run. Because that is what causes these single point of failures. The leadership of the organization decides what budget, quality demands and hires that will be done. The rest follows from there. Just take a look at the performance difference between United Launch Alliance (ULA) and SpaceX. The latter had less money to begin with asfaik, but do perform better. Bytesock (talk) 11:22, 12 May 2016 (UTC)[reply]