Trying to apply multiple formula to a cell, works great, but how on earth do i fill down !

**Andy Wiles** · 06-22-2020, 12:21 PM

Afternoon All.

Hope we are all well in this crazy old world.

I'm hoping that I can be cheeky enough to ask for a bit of help with some excel formula work.
to give you some background, I work in telecoms so therefore any numbers that i refer to are generally telephone numbers in the UK.

I’ve attached my file which wasn't originally set up by me, so its had to be unpicked a little.

The blue/Purple cells appear to be some logic that was put together several years ago to define if a phone number is a Platinum, Gold, Silver, Bronze or standard number.
It works fine for whatever number you type into cell B2, as it automatically puts into cell D2 the Category that the spreadsheet defines the number is. My target out of this piece of work is to have a list down Column B starting with 08444430000 and going up to 08444439999 and against each of those cells, in Column D I am looking for the category that the number would be placed into

sorry if it’s a dumb easy thing, I've pondered and played for hours and just can’t get my head around it.
So anything you can help with would be beyond amazing

**PKowalik** · 06-22-2020, 01:53 PM

I have no time at the moment to play around with your formulas, but, in my opinion you should adjust the formulas which are in I3:R12 and in T7:T9. They should be placed in row 2 starting from S2. Then, if you change references in I2:R2 to the number cell from $B$2 to $B2 and you put the sum which is in S12 in H2, it will be OK. If you have your final sum "evaluating" the number in H2, and all the formulas necessary for evaluation from I2 to some cell in row 2, then copying down/filling down the range H2:some cell in row2 will give what you need.

**mikeava** · 06-22-2020, 01:59 PM

Not sure if this is what you want but --- In your formula for Column D are you always looking at the value in S12?
If so just put a $ before the S and before the 12 then you will be able to copy the formula down.

=IF($S$12<10,"Standard",IF($S$12<14,"Bronze",IF($S$12<16,"Silver",IF($S$12<21,"Gold","Platinum"))))

**PKowalik** · 06-22-2020, 02:25 PM

@mikeava, Andy wants something different. He want to take phone numbers from column B, split them into separate numbers like in I2:R2, perform some "evaluations" as in the formulas with the blue background, and obtain, as a final result, for each phone number another number calculated like in S12.

OK, now here is a proposition of rearrangement of the sheet. All calculations necessary for analysis of numbers from column be are now made in a single row (see Sheet2 new).
Let's consider row 2.

H12 contains =SUM(S2:BI2) - the overall "measure", translated to the colour of the number in B2.

I2:R2 contain formulas extracting particular digits of the number in B2. The formulas are the same as in the original sheet.

S2:B12 contain formulas analysing the number in B2. Formulas are the same as in the original sheet, moved from non-empty cells of the range I3:R11. The only difference is =IF((T7+T8+T9)=3;3;0) from N10 which is now =IF((BK2+BL2+BM2)=3;3;0) in BE2. Cells BK2, BL2, BM2 contain auxiliary formulas moved from T7, T8, T9, respectively.

Unlike the original fomulas, the new formulas have no dollar signs for correct copying down.

Sums from J3:J11 are not implemented however, because they not really necessary. They are nothing more than "intermediate" sums which are used just to calculate the total value.

I hope I could help.

Trying to apply multiple formula to a cell, works great, but how on earth do i fill down !

LinkBack

Thread Tools

Rate This Thread

Display

Trying to apply multiple formula to a cell, works great, but how on earth do i fill down !

Re: Trying to apply multiple formula to a cell, works great, but how on earth do i fill do

Re: Trying to apply multiple formula to a cell, works great, but how on earth do i fill do

Re: Trying to apply multiple formula to a cell, works great, but how on earth do i fill do

Thread Information

Users Browsing this Thread

Similar Threads

Addin works great in all files except the one I want it to work in

[SOLVED] Code works great until the last day of the month

[SOLVED] Code works great...UNTIL i call it from another sheet- Why?

excel 2007 formula works great but when open in 2010 returns##

Macro works great in 2007 but not in older excel format

Countdown Timer won't run in a userform but works great on a worksheet.

VBA Macro checks for duplicates,works great, but wish to add a select row

Bookmarks

Bookmarks

Posting Permissions