Clarification on Find Text, then Calculate

  • Thread starter Thread starter Teatro
  • Start date Start date
T

Teatro

I originally said that I was trying to convert everything on my spreadsheet
to gallons. This is incorrect. What I'm trying to do is report on the exact
quantities that were sold. My spreadsheet has already converted everything to
1 gallon quantities. I hope this table below will show what I'm trying to do:

A B C D E
Description Qty Expected Results of IF statement
2 Water 1 Gal 4.00 4 (finds “1 Gal†in A2 and divides D2 by 1)

3 Water 5 Gal 15.00 3 (finds “5 Gal†in A3 and divides D3 by 5)

4 Water 1 Qt .75 3 (finds “1 Qt†in A4 and multiplies D4 by 4)

My thanks to the folks who replied to my original post and my sincerest
apologies for my misstatement of what I was looking for.

I hope such an IF statement is possible; otherwise I will have to manually
go through about 1800 rows of data manually.

Tea
 
I originally said that I was trying to convert everything on my spreadsheet
to gallons. This is incorrect. What I'm trying to do is report on the exact
quantities that were sold. My spreadsheet has already converted everything to
1 gallon quantities. I hope this table below will show what I'm trying todo:

A B C D E
Description Qty Expected Results of IF statement
2 Water 1 Gal 4.00 4 (finds “1 Gal” in A2 and divides D2 by 1)

3 Water 5 Gal 15.00 3 (finds “5 Gal” in A3 and divides D3 by 5)

4 Water 1 Qt .75 3 (finds “1 Qt” in A4 and multiplies D4 by 4)

My thanks to the folks who replied to my original post and my sincerest
apologies for my misstatement of what I was looking for.

I hope such an IF statement is possible; otherwise I will have to manually
go through about 1800 rows of data manually.

Tea
Click to expand...

Well, if your data is always EXACTLY what you show above, and Gal and
Qt are the only 2 options:
=IF(MID(A2,9,2)="Qt",MID(A2,7,1)*B2*4,MID(A2,7,1)*B2)

If your data fluctuates or there are many choices in Col A, you'll
have to add to this. Or even do it a completely different way.
 
Hi,
Try this modified, (though still lengthy) formula in E2 and copy down.
This assumes that <1 Gal, 5 Gal, 1 Qt > are the only 3 possibilities.

=IF(ISNUMBER(FIND("5 Gal",A2)),D2/5,IF(ISNUMBER(FIND("1
Gal",A2)),D2,IF(ISNUMBER(FIND("1 Qt",A2)),D2*4,"Unknown Qty")))

Note that this page places unwanted spaces at the wrap points, which have to
be removed after pasting into XL.

If this doesn't do what you want, please tell me what it does wrong.

Regards - Dave.
 
Works perfectly, Dave! Thank you so much! You have saved me hours of work and
probably baldness as well...from pulling out my hair!

Tea
 
Back
Top