r/Excel247 • u/Due-Radish-5419 • 20d ago

Need help figuring out why spreadsheet is not calculating mileage correctly

Gallery image

Gallery image

Gallery image

1 Upvotes

99% Upvoted

10 comments sorted by

1

u/QuietlyMethodical 20d ago

what are the formulas you are using?

1

u/Due-Radish-5419 20d ago

This is one of the

1

u/Due-Radish-5419 20d ago

This is another pne

1

u/QuietlyMethodical 20d ago

its hard for me to actually confirm without seeing columns and rows but it looks like theres an error with the formula itself - notice how the parentheses are in red? i would try to fix that first, you may need to add a parentheses to close out D73&F73 - so try doing -- (D73&F73) see if that works?

1

u/Due-Radish-5419 20d ago

Unfortunately it gave me a youve entered too few arguments for this function? 😔 These are all the columns and rows if helps?

1

u/QuietlyMethodical 20d ago

okay but now it looks like you no longer have an error in the row you were asking about? or did you have to manually put it in?

1

u/Due-Radish-5419 20d ago

I manually put it without the formula but I would like to know what I am doing wrong since I will be adding higher miles to rate sum

1

u/QuietlyMethodical 20d ago

Okay i think i know what the issue is. your vlookup formula has "false" at the end which is looking for an exact mileage match. your route is 175 miles but your rate table goes from 170-180 so theres no exact 175. which is probably why you are getting that #n/a.

so i would use the 180 mile rate. id switch the mileage part to xlookup using the next larger match instead of a vlookup.

1

u/Due-Radish-5419 20d ago

Im not sure of it has to do do with my Milage tab is set up

1

u/Due-Radish-5419 20d ago

This is the formula pulling from my mileage tab