Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find text, then calculate
I have a spreadsheet that has 3 sizes in a text string in cells. For example,
A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal. I want to create an IF statement that looks for those sizes in the Column A, and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm trying to convert everything to gallons). I've tried multiple formulas, but the best I can do is have it report true or false. TIA. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find text, then calculate
Hi,
With your data as given, try the following into E2 and copy down. =IF(ISNUMBER(FIND("Gal",A2)),D2*LEFT(A2, FIND(" ",A2)-1),D2*LEFT(A2, FIND(" ",A2)-1)/4) Regards - Dave. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find text, then calculate
=IF(A2="1 Gal",D2,IF(A2="5 Gal",D2/5,IF(A2="1 Qt",D2*4,"invalid input")))
-- David Biddulph "Teatro" wrote in message ... I have a spreadsheet that has 3 sizes in a text string in cells. For example, A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal. I want to create an IF statement that looks for those sizes in the Column A, and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm trying to convert everything to gallons). I've tried multiple formulas, but the best I can do is have it report true or false. TIA. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find text, then calculate
Still only get either "invalid input" (from your formula) or FALSE (when I
remove the invalid input text). The cell references you have listed are correct, as is the text, but I still can't get it to work except to return FALSE (even when it finds the 5 Gal or the 1 Qt in the text). "David Biddulph" wrote: =IF(A2="1 Gal",D2,IF(A2="5 Gal",D2/5,IF(A2="1 Qt",D2*4,"invalid input"))) -- David Biddulph "Teatro" wrote in message ... I have a spreadsheet that has 3 sizes in a text string in cells. For example, A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal. I want to create an IF statement that looks for those sizes in the Column A, and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm trying to convert everything to gallons). I've tried multiple formulas, but the best I can do is have it report true or false. TIA. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarifying information
I originally said that I was trying to convert everything to gallons. This is
incorrect on further review. 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) "Teatro" wrote: Still only get either "invalid input" (from your formula) or FALSE (when I remove the invalid input text). The cell references you have listed are correct, as is the text, but I still can't get it to work except to return FALSE (even when it finds the 5 Gal or the 1 Qt in the text). "David Biddulph" wrote: =IF(A2="1 Gal",D2,IF(A2="5 Gal",D2/5,IF(A2="1 Qt",D2*4,"invalid input"))) -- David Biddulph "Teatro" wrote in message ... I have a spreadsheet that has 3 sizes in a text string in cells. For example, A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal. I want to create an IF statement that looks for those sizes in the Column A, and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm trying to convert everything to gallons). I've tried multiple formulas, but the best I can do is have it report true or false. TIA. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find text, then calculate
My guess is that the content of your cell isn't what you've said it is.
Perhaps you've got leading or trailing spaces, or some other non-printing characters? What does =LEN(A2) return? Should be 5 for the 1 Gal and 5 Gal cases and 4 for the 1Qt. -- David Biddulph "Teatro" wrote in message ... Still only get either "invalid input" (from your formula) or FALSE (when I remove the invalid input text). The cell references you have listed are correct, as is the text, but I still can't get it to work except to return FALSE (even when it finds the 5 Gal or the 1 Qt in the text). "David Biddulph" wrote: =IF(A2="1 Gal",D2,IF(A2="5 Gal",D2/5,IF(A2="1 Qt",D2*4,"invalid input"))) -- David Biddulph "Teatro" wrote in message ... I have a spreadsheet that has 3 sizes in a text string in cells. For example, A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal. I want to create an IF statement that looks for those sizes in the Column A, and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm trying to convert everything to gallons). I've tried multiple formulas, but the best I can do is have it report true or false. TIA. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find text, then calculate
Sorry, I didn't mean to imply that the only text in A2 was 1 Gal, 5 Gal or 1
Qt. 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) "David Biddulph" wrote: My guess is that the content of your cell isn't what you've said it is. Perhaps you've got leading or trailing spaces, or some other non-printing characters? What does =LEN(A2) return? Should be 5 for the 1 Gal and 5 Gal cases and 4 for the 1Qt. -- David Biddulph "Teatro" wrote in message ... Still only get either "invalid input" (from your formula) or FALSE (when I remove the invalid input text). The cell references you have listed are correct, as is the text, but I still can't get it to work except to return FALSE (even when it finds the 5 Gal or the 1 Qt in the text). "David Biddulph" wrote: =IF(A2="1 Gal",D2,IF(A2="5 Gal",D2/5,IF(A2="1 Qt",D2*4,"invalid input"))) -- David Biddulph "Teatro" wrote in message ... I have a spreadsheet that has 3 sizes in a text string in cells. For example, A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal. I want to create an IF statement that looks for those sizes in the Column A, and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm trying to convert everything to gallons). I've tried multiple formulas, but the best I can do is have it report true or false. TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find formula to calculate a year's salery | New Users to Excel | |||
Find and Calculate Function? | Excel Discussion (Misc queries) | |||
find the same entries and calculate sum | Excel Worksheet Functions | |||
Find dates to calculate amount of work per day? Possible? | Excel Discussion (Misc queries) | |||
Find duplicates then calculate..??? | Excel Worksheet Functions |