Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification on Find Text, then Calculate
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification on Find Text, then Calculate
On Jun 18, 10:43 am, Teatro wrote:
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 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification on Find Text, then Calculate
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification on Find Text, then Calculate
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 "Dave" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification on Find Text, then Calculate
You're welcome - but not bald already? that's not fair!
Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find text, then calculate | Excel Worksheet Functions | |||
Subtotal - Clarification | New Users to Excel | |||
If Then Statement Clarification | Excel Discussion (Misc queries) | |||
Beyond VLOOKUP Clarification | Excel Worksheet Functions | |||
If function clarification | Excel Worksheet Functions |