Formula help request for changing text in a cell into a currency value and totalling the a

[Bobby5]

Hi,

Got a problem and need help please.

Did excel many years a go and have now forgot most of it.

The scenario is:

Got text in a row/column (cell) and for each new row (cell) of text that is created it must show a currency value based on text input. So say that in a row/column (cell) it is days: Monday = £100, Tuesday = £100, Wednesday = empty (blank) and so on for the week or however may rows are completed with text in them = £100. Then I want to total the amount of rows with text in them, so if 49 rows have text in at £100 this would total £4,900 and so on for each column.

But I also need it to recognise when a new row (cell) has text added it will add the amount of stated or required i.e. £100 but could be £200 depending on the required setting in formula.

Any advice?

Thanks

[alansidman]

Please upload a sample spreadsheet so that we can provide you with an accurate response.

[Bobby5]

Please upload a sample spreadsheet so that we can provide you with an accurate response.

That's just the point I can't work the formula to complete the worksheet, so all you would get is the text in a box.

[Bobby5]

£2,500.00

Avon
Bath
Bradley Stoke
Bristol
Clevedon
Keynsham
Nailsea
Portishead
Thornbury
Weston super Mare
Yate


Right best I can do here at the moment is show the above.

Each row of text = £250 - so if a row has text in it = £250.00 if it's blank =£0.00 the amount then totals above to equate to £2,500.00 for the amount of rows with text in them.

Each Town is worth £250.00:

Bath
Bradley Stoke
Bristol
Clevedon
Keynsham
Nailsea
Portishead
Thornbury
Weston super Mare
Yate

[BB1972]

That's just the point I can't work the formula to complete the worksheet, so all you would get is the text in a box.

Bobby, that's fine - upload a sample workbook, showing the type and layout of your data, and clearly indicating the answer you would expect to get. Text in a cell, showing us the answer that should be there, is fine.

[Bobby5]

Hi BB1927,

Thanks, well that is as the above post.

It shows the amount expected from the cells with text in if each cell with text (a town name in) is worth £250.00 = £2,500.00

[Kevin UK]

Hi Bobby5

Not sure if I no what you mean!
How about something along these lines: =COUNTA(A:A)*250

[TheExcelphile]

Hi Bobby!

Here is a workbook where I've put in two options. You can choose whichever one you want based upon your needs.

If the value for each town is going to be the same, you can use the formula given in A1.
If the value for each town is going to change, you can use the formula given in B1.

Don't forget to click the little star to the bottom left of this post if you feel I helped!

[Kevin UK]

Here is a example Bobby5

[Bobby5]

Thanks Kevin UK and The ExcelPhile, both worked and provided what I needed, much appreciated.

[Bobby5]

Ah, great both worked, jst come accross another problem. My fault entirely.

There has to be a minimum value of £1,000

i.e. if there are less than four towns to make £1,000 from four towns worth £250 each, then the minimum should be £1,000.

So only two towns would normally only make £500, but the minimum is £1,000.

Hope that makes sense.

[BB1972]

Ah, great both worked, jst come accross another problem. My fault entirely.

There has to be a minimum value of £1,000

i.e. if there are less than four towns to make £1,000 from four towns worth £250 each, then the minimum should be £1,000.

So only two towns would normally only make £500, but the minimum is £1,000.

Hope that makes sense.

It doesn't really. There are 10 towns in your above list. Are there different lists that the formula you're looking for is to applied to? If not, what criteria are used to select the possibly-less-than-four-towns-from-the-list-of-10?

Again, you should upload a sample file, showing the type and layout of your data, and clearly indicating the answer you require, to help us help you. Text in a cell is often more helpful than text in a paragraph.

[TheExcelphile]

You could try the MAX formula:

=MAX(1000,formula given earlier)

[Kevin UK]

Hi Bobby5

Thanks for the feed back.

Update the formula in Post #9 spreadsheet to: =MAX(COUNTA(A2:A21)*250,1000)

Kevin