Formula calculating a projected Charge

Muddypaws

New Member
Joined
Dec 3, 2009
Messages
28
Its a difficult one to explain but I have spent hours trying to work this out and I have literally hit a brick wall. Not sure how to upload the spreadsheet to make it easier to explain so apologies. I have inherited this and I just dont know how to work this out and I know what I want to work out but not sure how to do it.

First one is to look up all zone one data (tab2) and return number of planned jobs and restoration jobs for that zone (tab1). I have 9 zones in all. 17 I know is wrong as thats the total for all, what I need is the 17 split between Tyne & Wear and West Yorkshire Etc


Tab 1


Live Generator CountTotal Days ConnectedTotal Running Costs
PlannedRestorationPlannedRestorationPlannedRestoration
Tyne & Wear17
West Yorkshire
South Yorkshire
Teesside
Humber Estuary
Northumberland + Co Durham
Yorkshire Moors + Wolds
Yorkshire Dales
North Lincolnshire

<tbody>
</tbody>



Tab 2

ZONERESTORATION/PLANNED
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
03-TEESSIDERESTORATION
03-TEESSIDERESTORATION
03-TEESSIDERESTORATION
03-TEESSIDERESTORATION

<tbody>
</tbody>





Second one is and this is totally driving me nuts. Tab 2 needs to look at tab 1. Look for all 100kva generators, and return how much it will cost for the number of days hire. So day 1 is say £677.85 and all days after that is £29.61. So if a 15kva generator has run 22 days then it will return £1299.66. Problem is there on different tabs.

Probably none of the above makes sense and I dont know how to upload the spreadsheet


Tab 1

Generator Size1st DaySubs Days1 Week Hire
15£677.85£29.61£855.51
30£682.90£35.69£897.04
50£682.90£35.69£897.04
80£722.40£53.39£1,042.74
100£722.40£53.39£1,042.74
150£1,025.53£90.57£1,568.95
200£1,025.53£90.57£1,568.95
250£1,535.61£152.08£2,448.09
320£1,535.61£152.08£2,448.09
500£2,134.46£243.95£3,598.16
750£2,918.10£367.19£5,121.24
1000£2,918.10£367.19£5,121.24
1250£3,499.55£501.49£6,508.49

<tbody>
</tbody>



Tab 2

ZONERESTORATION/PLANNEDGENERATOR SIZE (KVA)DATE/TIME CONNECTED TO THE NETWORKDAYS CONNECTEDPROVISIONAL COSTS
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION10029/06/2018 00:0022£1,299.66

<tbody>
</tbody>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Answer to #1 ...

First change all of the names in tab 1 to match tab 2. For example, Change "Tyne & Wear" to "02-TYNE & WEAR". Or you can change them all to "Tyne & Wear", bottom line is they have to match in order for this formula to work.

Second, I am assuming "17" in your example is in B3 and the columns in tab 2 are A and B. Also, I am assuming your sheet names are "Sheet1" & "Sheet2" If not, the formula will have to be modified.

Finally, insert the first formula into B3 and drag it down auto fill to the bottom. Paste the second formula into B4 and drag it down to the bottom.
Code:
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!B2)

Code:
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!C2)

Hope this helps.
 
Upvote 0
Answer to #1 ...

First change all of the names in tab 1 to match tab 2. For example, Change "Tyne & Wear" to "02-TYNE & WEAR". Or you can change them all to "Tyne & Wear", bottom line is they have to match in order for this formula to work.

Second, I am assuming "17" in your example is in B3 and the columns in tab 2 are A and B. Also, I am assuming your sheet names are "Sheet1" & "Sheet2" If not, the formula will have to be modified.

Finally, insert the first formula into B3 and drag it down auto fill to the bottom. Paste the second formula into B4 and drag it down to the bottom.
Code:
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!B2)

Code:
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!C2)

Hope this helps.


I have tried it and it does not work, its a bit of a hatchet job which is why its confusing me and no one knows how to do it. hope this helps so in effect Tyne and Wear should have 5 restorations and Teeside should have 3 restorations and West Yorkshire 11 planned.

Sheet 1 (is my summary)

Live Generator CountTotal Days Connected Total Running Costs
Planned Restoration Planned Restoration Planned Restoration
02-TYNE AND WEAR0
06-WEST YORKSHIRE
08-SOUTH YORKSHIRE
03-TEESSIDE
01-NORTHUMBERLAND & COUNTY DURHAM
05-YORKSHIRE MOORS & WOLDS
04-YORKSHIRE DALES




<colgroup><col><col span="6"></colgroup><tbody>
</tbody>


Sheet 2 (is my data)

ZONEFAULT/PLANNED WORK
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
04-YORKSHIRE DALESRESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
04-YORKSHIRE DALESRESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
06-WEST YORKSHIREPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
05-YORKSHIRE MOORS & WOLDSRESTORATION
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Try this one in B3...

=COUNTIFS(Sheet2!A:A,"02-TYNE AND WEAR",Sheet2!B:B,"Planned")

This should work for your summary page, but you will have to change the zone and planned/restoration for each cell you put it in.
 
Upvote 0
I have tried it and it does not work, its a bit of a hatchet job which is why its confusing me and no one knows how to do it. hope this helps so in effect Tyne and Wear should have 5 restorations and Teeside should have 3 restorations and West Yorkshire 11 planned.

Sheet 1 (is my summary)

Live Generator CountTotal Days ConnectedTotal Running Costs
PlannedRestorationPlannedRestorationPlannedRestoration
02-TYNE AND WEAR0
06-WEST YORKSHIRE
08-SOUTH YORKSHIRE
03-TEESSIDE
01-NORTHUMBERLAND & COUNTY DURHAM
05-YORKSHIRE MOORS & WOLDS
04-YORKSHIRE DALES

<tbody>
</tbody>


Sheet 2 (is my data)

ZONEFAULT/PLANNED WORK
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
04-YORKSHIRE DALESRESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
04-YORKSHIRE DALESRESTORATION
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIRERESTORATION
06-WEST YORKSHIRERESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
06-WEST YORKSHIREPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
01-NORTHUMBERLAND & COUNTY DURHAMPLANNED
01-NORTHUMBERLAND & COUNTY DURHAMRESTORATION
02-TYNE AND WEARRESTORATION
02-TYNE AND WEARRESTORATION
03-TEESSIDERESTORATION
05-YORKSHIRE MOORS & WOLDSRESTORATION
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED
06-WEST YORKSHIREPLANNED

<tbody>
</tbody>

i WOULD LIKE TO SAY IN BIG CAPITALS A MASSIVE THANK YOU FOR THIS, IT TOOK A WHILE BUT I GOT THERE IN THE END, EXCEL IS A VERY DIFFICULT APPLICATION FOR ME TO USE, BUT ONCE I HAVE A BIT OF GUIDANCE I SORT OF CAN GET ON WITH IT. A HUGE THANK YOU AND A VERY HAPPY BUNNY THIS END
 
Upvote 0
If I, which I do need to add up a column in the same way rather than the numbe rof instances do i just use "SUMIF"
 
Upvote 0
Yes! However, if you have multiple criteria, you may have to use SUMIFS.

For example, we had to use COUNTIFS in the first problem because you were looking at the zone and planned/restoration. If you have multiple criteria again, make sure it is SUMIFS.
 
Upvote 0
For Question #2

Try inserting the following formula into your "Provisional Cost" column on Sheet 2

=VLOOKUP($C2,Sheet1!$A:$B,2,FALSE)+VLOOKUP(Sheet2!$C2,Sheet1!$A:$C,3,FALSE)*(Sheet2!$E2-1)

Again I am making assumptions on which columns your data lies in, so if you have issues let me know.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top