Member Since: 25th Jun 2009
24th Sep 2016
If those rates change, you'll have to change all the formulas that contain those numbers. It would be better to maintain the numbers in a visible table.
Suppose A1:B4 contain the table
And suppose G4 contains the amount, then in the cell for the percent enter the formula
The TRUE means do an approximate match of the amount in G4 in the first column (A2:A4, ie excluding the header row 1) and the 2 means get the result from the second column, ie the percentage.
Now the table is obvious and the formula won't have to be changed - unless of course you add more levels.
15th Mar 2013
Some replies from LinkedIn on Web app Excel
"Can someone suggest a program for converting an Excel spreadsheet application into a web application?"
1) EASA allows a spreadsheet author auto-generate a web app using the original spreadsheet as the logic engine. The app can then be published to authorized users.
It simply runs excel.exe on the back end (brokered via web services), so there is never a problem with add-ins, macros, or complex formulae not being recognized.
“All the work is done in the browser, (so) the problems we can resolve can only be a certain physical size,” Gary Miller said.
3) HyperNumbers http://hypernumbers.com/
4) DISCOM platform for spreadsheet composition (at research stage) www.m3s.it
5) Spreadsheet on Cloud www.seeta.in/j/publications.html
13th Jul 2012
As 'new' said, break it down to small parts to test.
First check that matches exist
btw why not $B$4 ?
12th Feb 2010
Solver, but you need more data
Simon is right, Solver it is.
Not part of basic Excel, but a free separate addin - not part of the Analysis Toolpak.
You need to know not just the cost but the return of each expenditure and whether there are any related constraints eg on time or personnel or sales caps or other resources.
Otherwise, the decision would be obvious - buy the most profitable one.