I'm looking for help on a formula that allows me to calculate freight charge based on furthest distance. We charge freight based on the total quantity to the furthest distance. In my example i used only 2 matching loads but there can be more.
thanks in advance.
I have updated my spreadsheet with the formula i came up with so far but it just doesent quite work.
anyone want to give it a shot? i really need a hand so i can move on.
thanks is advance.
Hi simpson,
This is what I came up with... it's an array-formula, so you have to use CTRL-SHIFT-ENTER, instead of just ENTER...
=IFERROR(((SUM($J6:$N6)*INDEX($AE$6:$AE$13,MATCH(MAX(($F$6:$F$13=$F6)*$AC$6:$AC$13),($F$6:$F$13=$F6)*$AC$6:$AC$13,0),1))+SUM($O6:$T6)*INDEX($AF$6:$AF$13,MATCH(MAX(($F$6:$F$13=$F6)*$AC$6:$AC$13),($F$6:$F$13=$F6)*$AC$6:$AC$13,0),1))*(100%+$W6),"")
Let me know if you are getting what you need...
Dennis
I'm getting a # N/A error.
any ideas?
Did you use CTRL-SHIFT-ENTER?
After you copy the formula, make sure to be in edit mode (if not, press F2), then press all together CTRL-SHIFT-ENTER... not just ENTER... you should see some magical curly brackets {=formula}
Any luck?
Here's my version...
Home now. will check it shortly. thanks
I got the formulas installed in my sample and they work. Now i just need to add them to my actual spreadsheet and varify they will work.
I have never had to paste a formula that way {with code tags} why is this one different?
It's not code tags... that's showing that you are using arrays... like I said, you have to use array-enter (CTRL-SHIFT-ENTER), instead of just ENTER... when you do this you will see the curly brackets and the ranges will act as arrays... I suggest you look into them... very powerful stuff...
I'll get it transfered over to my actual workbook today. thanks so much. I'll let you kow how it goes.
I got it transfered and it works, however, in my sample the numbers in column AC were just values. in my actual workbook they are generated by a formula. In the blank cells between loads that formula generates a "blank" cell if there is no load info in that row. This creates a #value error in the formula. any way to fix this?
I added a new sample.
Last edited by simpson; 11-30-2012 at 12:22 PM.
Hi simpson,
There are 2 ways that we can fix this... a simple way and a little less simple way...
The simple way is to keep your formula, but delete the formulas in the blank cells... the big formula doesn't like mixing numbers and text.
The less simple way, is to tweak your formula so that instead of "", change it 0.
I know, I know, there are 0's now... well, we can change the number format for that column to not show the 0's.
Here's how: (I apologize if you know this already)
Number format (right-click, Format Cells, Number) has 4 parts... positives;negatives;zeros;text (separated by semicolons)
So if you choose say Accounting, then go back to Format Cells, Number and choose Custom instead, it will show this...
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
Where the first item _($* #,##0.00_) shows what positives will look like (research what these symbols mean... for example # is an optional number placement, while 0 is permanent number placement... _) means a space the width of a parenthesis to align with negative numbers... etc... each symbol represents something)
The second item _($* (#,##0.00) show what negatives will look like (hence the extra parentheses)
The third item _($* "-"??_) is what your zeros will look like... in this case a small - somewhat off from the right
The fourth item is what you text will look like
So, to make a long story short, you can change these types... in your case, highlight AC6:AC13, Format Cells, Number, Custom and change the Type to...
0;;; - this will force your 0's to disappear because we didn't give it a format...
Hope this helps...
PS I didn't meant to show columns AH and AI... I was just testing how to get the charges... then I placed these within the big formula... you can delete AH and AI...
Last edited by djapigo; 11-30-2012 at 02:03 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks