Looking for an Excel Formula that will solve my issue of trying to calculate fees based on the value entered in a specific cell with the conditional fees being based on pallet quantities and order quantities.
Our take
I would like to create a formula in column B to calculate fees based on the qty entered in column C. There is a small order fee of $126.31 if the qty in column C is 4 or less (which I have 4 in column M - may be redundant). Next fee that could potentially be added to the small order fee is for our oversized pallet fee (column I) and crate fee (column K). This fee is charged per qty entered in column J. So if a qty of 3 is entered in C16, it would need to add the small order fee as well as the oversized pallet fee and the crate fee. If a qty of 12 is entered in C16, it would only calculate the oversized pallet fee and crate fee 1 time. If a qty of 24 is entered in C16, it would need to charge the oversized pallet fee and crate fee 2 times. As it's more than 18, but less than 36.
Additionally, the pallet quantity varies from model number to model number so it'd need to look at the value in column J and use that is the increments to determine how many times the fees should be charged.
Any and all help would be greatly appreciated! I have thousands of rows of data to apply this to and doing it manually would just take forever. Thanks again!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Calculating costs using check boxes and set criteriaPreviously posted on the wrong feed, so reposting in here now. I'm looking to set up a sheet where I can track attendance to an event, but also the revenue generated from entrance fees. I've set up a simple table that lists cost pp and check box once confirmed/paid (simplified in picture). What I then need to do is set out formula for the following: Calculate the total (gross) amount received. This is currently with =SUMIF($C$3:$C$7,TRUE, $B$3:$B$7) Calculate the actual (net) amount recieved, but this needs to have the Service Costs subtracted first, which is 6.95% of the Fee, plus an additonal £0.59. The formula I have is =SUM(SUMIF($C$3:$C$7,TRUE,$B$3:$B$7)*0.9305)-0.59 but this only subtracts the £0.59 once, whereas I need this to be per row/fee. https://preview.redd.it/7ln6646rz3yg1.png?width=487&format=png&auto=webp&s=e9f2283823da6e0a68945cf865ff0ab75bb95117 Any thoughts? submitted by /u/i_am_smedley [link] [comments]
- Lookup in table, return a formulaIs there a way to lookup a name/value of a cell in a table and the resulting answer is a formula? I am wanting to build a calculation, which could use 1 or more variables, depending on the value of a dropdown menu (Im assuming a drop down, since that would force the exact name/format of the formula callout). https://preview.redd.it/bhib489h9fpg1.png?width=1115&format=png&auto=webp&s=c79605dbb3388ec2e3471e60aecb4dd1f304bcfd Above is a quick and dirty example. If in Cell8, there is a drop down that listed the values in column J (1 Face Area, Length Face Area, etc.). In Cells D-F8, a user can input the thickness, length & width. Cell H8 would lookup the value from C8, reference the table from J5:K11, and after finding the formula, it will do the appropriate calculation. What I listed as a formula is just text for the example. I am open to adjusting as needed. This is just an example. I know I could do a bunch of imbedded IF statements in this example, but assume the table from J5:K11, could be many many lines. The table could be as many columns and rows as needed. Further, the example is just a single line (row 8), the intent would be to have many (10's, 100's or even 1,000's) rows of data, so calculating the answers and looking those up the table wouldn't work, like a single line entry. Is this even possible? I would prefer to accomplish this without VBA, if possible. EDIT: Here is another version of the example. As you can see I am planning on having multiple lines of entries. Some will have the same dropdown formula requested (i.e. Length Face Area), while others may never be requested. Hopefully that clarifies a bit. Also, while I would prefer to do without VBA, if that is the "easiest" solution, I am not opposed to entertaining. https://preview.redd.it/kqil9xiljfpg1.png?width=759&format=png&auto=webp&s=71f6d68f69bd0f6a5550d256b344032d7bc9ccbb submitted by /u/ernstm63 [link] [comments]